sybsystemprocs

Notesysprocsdev is the default system name for this device. However, it is frequently referred to as the sybsystemprocs device, as in the Adaptive Server attribute screen, since it stores the sybsystemprocs database.

Verify that the sybsystemprocs database is large enough. For an upgrade, the recommended minimum size for sybsystemprocs is the larger of 105MB, or enough free space to accommodate the existing sybsystemprocs database, and the largest catalog that is to be upgraded, plus an additional 10 percent of the largest catalog’s size. The additional 10 percent is for logging upgrade changes.

You may need more space if you are adding user-defined stored procedures. 105MBs accommodate additional internal data structures, but does not account for the possibility of a large number of user-defined system procedures.

If your sybsystemprocs database does not meet these requirement and you have enough room on the device to expand the database to the required size, use the alter database command to increase the database size.

Use sp_helpdb to determine the size of the sybsystemprocs database:

1> sp_helpdb sybsystemprocs
2> go

Use sp_helpdevice to determine the size of the sysprocsdev device:

1> sp_helpdevice sysprocdev
2> go

If the db_size setting is less than the required minimum, you must increase the size of sysprocdev.


Increasing the size of the sybsystemprocs database

If your current sybsystemprocs database does not have the minimum space required, you have two options for creating a new database with sufficient space for the upgrade:

To enlarge the sybsystemprocs database:

  1. If you do not have a current backup, create one.

  2. In isql, use alter database to increase the size of the sybsystemprocs database. For example:

    1> use master
    2> go
    1> alter database sybsystemprocs on sysprocsdev=40
    2> go
    

    In this example, “sysprocsdev” is the logical name of the existing system procedures device and “40” is the number of megabytes of space to add.

    If the system procedures device is too small, you may receive a message similar to the following when you try to increase the size of the sybsystemprocs database:

    Could not find enough space on disks to extend database sybsystemprocs
    

    If there is space available on another device, you can expand sybsystemprocs to a second device, or initialize another device that is large enough. For instructions on creating a larger sybsystemprocs device, see “Increasing device and database capacity for system procedures”.

  3. To verify that Adaptive Server has allocated more space to sybsystemprocs, issue:

    1> sp_helpdb sybsystemprocs
    2> go
    

When the system procedures database is large enough to accommodate the increased size of the sybsystemprocs database, continue with the other pre-upgrade tasks.


Increasing device and database capacity for system procedures

If you cannot fit the enlarged sybsystemprocs database on the system procedures device, increase the size of the device and create a new database.

This procedure involves dropping the database. For more information on drop database, see the Reference Manual.

WARNING! This procedure removes all stored procedures you have created at your site. Before you begin, save your local stored procedures using the defncopy utility. See the Utility Guide for more information on Adaptive Server utilities.

To create a larger system procedures device (sysprocsdev):

  1. Determine which device or devices you need to remove.

    WARNING! You must not remove any device that is in use by database other than sybsystemprocs, or you will destroy that database.

    select d.name, d.phyname 
    from sysdevices d, sysusages u 
    where u.vstart between d.low and d.high 
    and u.dbid = db_id("sybsystemprocs") 
    and d.status & 2 = 2 
    and not exists (select vstart 
        from sysusages u2
        where u2.dbid != u.dbid 
        and u2.vstart between d.low and d.high)
    

    where d.name is the list of devices to remove from sysdevices and d.phyname is the list of files to remove from your computer.

    The “not exists” clause in this query excludes any devices that are used both by sybsystemprocs and other databases.

    Note the names of the devices; you will need them in the coming steps.

  2. Drop sybsystemprocs:

    use master
    go
    drop database sybsystemprocs
    go
    
  3. Remove the device or devices using sp_configure:

    sp_configure "allow updates", 1
    go
    
    
    delete sysdevices
    where name in ("d.name")
    go
    
    
    sp_configure "allow updates", 0
    go
    

    where d.name is the name of each devices returned in step 1.

    NoteEach device name must have quotes. For example, “devname1”, “devname2”, and so on.

    If any of the named devices are OS files rather than raw partitions, use the appropriate OS commands to remove those files.

  4. Remove all files for the list of d.phyname returned in step 1.

    NoteFile names may not be complete path names. If they are relative paths, they will be relative to the directory from which your server was started.

  5. Find another existing device that meets the requirements for additional free space, or use a disk init command similar to the following to create an additional device for sybsystemprocs:

    1> use master
    2> go
    
    
    1> disk init
    2> name = "sysprocsdev",
    3> physname = "$SYBASE/work/sysproc.dat",
    4> vdevno = 9,
    5> size = 51200
    6> go
    

    where $SYBASE/work/ is the full, absolute path to your system procedures device. You cannot use the environment variable $SYBASE in this command.

    NoteThe number for vdevno must be available. For information about determining whether vdevno is available, see the System Administration Guide.

    The size you provide should be the number of megabytes of space needed for the device multiplied by 512. disk init requires the size to be specified in 2K pages. In this example, the size is 105MB (105 x 512 = 53760). For more information on disk init, see the Reference Manual.

  6. Create a sybsystemprocs database of the appropriate size on that device, for example:

    1> create database sybsystemprocs on sysprocsdev = 105
    
    2> go
    
  7. Run the installmaster script in the old Sybase installation directory, as follows:

    isql -Usa -Ppassword -Sserver_name -i/old_dir/
    scripts/installmaster -oinstallmaster.out
    

You must reinstall the system stored procedures for use later in this procedure.