DB2 Server for VSE & VM SQL Reference (Version 7 Release 5)

SQLCA

unsigned char sqlcaid[8];

long

sqlcabc;

long

sqlcode;

short

sqlerrml;

unsigned char

sqlerrmc[70];

unsigned char

sqlerrp[8];

long

sqlerrd[6];

unsigned char

sqlwarn[11];

unsigned char

sqlstate[5];

};

#define SQLCODE sqlca.sqlcode

#define SQLWARN0

sqlca.sqlwarn[0]

#define SQLWARN1

sqlca.sqlwarn[1]

#define SQLWARN2

sqlca.sqlwarn[2]

#define SQLWARN3

sqlca.sqlwarn[3]

#define SQLWARN4

sqlca.sqlwarn[4]

#define SQLWARN5

sqlca.sqlwarn[5]

#define SQLWARN6

sqlca.sqlwarn[6]

#define SQLWARN7

sqlca.sqlwarn[7]

#define SQLWARN8

sqlca.sqlwarn[8]

#define SQLWARN9

sqlca.sqlwarn[9]

#define SQLWARNA

sqlca.sqlwarn[10]

#define SQLSTATE

sqlca.sqlstate

#endif

struct sqlca sqlca;

COBOL

01 SQLCA.

05 SQLCAID

PIC X(8).

05 SQLCABC

PIC S9(9) COMPUTATIONAL.

05 SQLCODE

PIC S9(9) COMPUTATIONAL.

05 SQLERRM.

49 SQLERRML PIC S9(4) COMPUTATIONAL.

49 SQLERRMC PIC X(70).

05 SQLERRP

PIC X(8).

05 SQLERRD

OCCURS 6 TIMES

PIC S9(9) COMPUTATIONAL.

05 SQLWARN.

10 SQLWARN0

PIC X(1).

10 SQLWARN1

PIC X(1).

10 SQLWARN2

PIC X(1).

10 SQLWARN3

PIC X(1).

10 SQLWARN4

PIC X(1).

10 SQLWARN5

PIC X(1).

10 SQLWARN6

PIC X(1).

10 SQLWARN7

PIC X(1).

10 SQLWARN8

PIC X(1).

10 SQLWARN9

PIC X(1).

10 SQLWARNA

PIC X(1).

05 SQLSTATE

PIC X(5).

Fortran

INTEGER*4

SQLCOD,

SQLERR(6),

SQLTXL*2

COMMON /SQLCA1/

SQLCOD,SQLERR,SQLTXL

CHARACTER

SQLERP*8,

SQLWRN(0:10),

SQLTXT*70

SQLSTT*5

COMMON /SQLCA2/

SQLERP,SQLWRN,SQLTXT,SQLSTT

358

SQL Reference

SQLCA

PL/I

DCL 1 SQLCA,

2 SQLCAID

CHAR(8),

2 SQLCABC

BIN FIXED(31),

2 SQLCODE

BIN FIXED(31),

2 SQLERRM

CHAR(70) VAR,

2 SQLERRP

CHAR(8),

2 SQLERRD(6)

BIN FIXED(31),

2 SQLWARN,

3 SQLWARN0

CHAR(1),

3 SQLWARN1

CHAR(1),

3 SQLWARN2

CHAR(1),

3 SQLWARN3

CHAR(1),

3 SQLWARN4

CHAR(1),

3 SQLWARN5

CHAR(1),

3 SQLWARN6

CHAR(1),

3 SQLWARN7

CHAR(1),

3 SQLWARN8

CHAR(1),

3 SQLWARN9

CHAR(1),

3 SQLWARNA

CHAR(1),

2 SQLSTATE

CHAR(5);

SQL Descriptor Area (SQLDA)

An SQLDA is a structure or collection of variables that is required for execution of

the SQL DESCRIBE statement, and may optionally be used by the OPEN, FETCH,

EXECUTE, and PUT statements. An SQLDA communicates with dynamic and

extended SQL; it can be used in a DESCRIBE statement, modified with the

addresses of host variables, and then reused in a FETCH statement. The DB2 Server

for VSE & VM Application Programming manual describes the use of an SQLDA.

The meaning of the information in an SQLDA depends on its use. In DESCRIBE

and Extended DESCRIBE, an SQLDA provides information to an application

program about a prepared statement. In EXECUTE, OPEN, PUT, and Extended

EXECUTE, Extended OPEN, and Extended PUT an SQLDA provides information

to the database manager about input host variables. In Extended EXECUTE,

FETCH and Extended FETCH, an SQLDA provides output information.

SQLDAs are supported in all languages, however predefined declarations are only

provided by Assembler, C, and PL/I. In these languages the SQL INCLUDE

statement can be used to provide a SQLDA declaration. A similar set of variables is

used for this purpose in REXX (see the DB2 REXX SQL for VM/ESA manual for

details).

Description of Fields

An SQLDA consists of four variables followed by an arbitrary number of

occurrences of a sequence of five variables collectively named SQLVAR. In OPEN,

FETCH, PUT, and EXECUTE, each occurrence of SQLVAR describes a host

variable. In DESCRIBE, they describe columns of a result table.

Appendix B. SQLCA and SQLDA

359

SQLDA

Table 20. Fields of SQLDA

Usage in DESCRIBE and

Usage in EXECUTE, FETCH, OPEN,

Extended DESCRIBE (set

PUT, and extended dynamic statements

Assembler or

by the database manager

of the same name (set by the user prior

PL/I Name

C Name

Data Type

except for SQLN)

to executing the statement)

SQLDAID

sqldaid

CHAR(8)

An 'eye catcher' for storage

For CCSID when the protocol is SQLDS,

dumps, containing 'SQLDA

the sixth position of this field must be set

to '+'; for example, 'SQLDA+ '¹ (See

SQLNAME in Table 21) Not used

otherwise.

SQLDABC

sqldabc

INTEGER

Length of the SQLDA,

Number of bytes of storage allocated for

equal to SQLN*44+16.

the SQLDA. Enough storage must be

allocated to contain SQLN occurrences.

SQLDABC must be set to a value greater

than or equal to 16+SQLN*44.

SQLN

sqln

SMALLINT

Unchanged by the database

Total number of occurrences of SQLVAR

manager. Must be set to a

provided in the SQLDA. SQLN must be

value greater than or equal

set to a value greater than or equal to

to zero before the

zero.

DESCRIBE statement is

processed. Indicates the

total number of

occurrences of SQLVAR.

SQLD

sqld

SMALLINT

For a SELECT statement,

The number of host variables described

the number of columns

by occurrences of SQLVAR to be used in

described by occurrences of

the SQLDA when executing this

SQLVAR (or, if USING

statement. SQLD must be set to a value

BOTH was specified on

greater than or equal to zero and less

DESCRIBE, twice the

than or equal to SQLN.

number of columns).

For a non-SELECT

statement, 0.

1: When SQLDS or DRDA protocols are being used, the database manager calculates the CCSID value of the user

data area before the first FETCH (or PUT) cursor operation. This CCSID value is ONLY recalculated on subsequent

FETCHes (or PUTs) if position 6 of the SQLDAID field has been set to '+'. Using this mechanism, it is possible to

dynamically change the CCSID value for an open cursor.

2: The field names are those present in an SQLCA obtained from an INCLUDE statement.

Fields in an Occurrence of SQLVAR

Table 21. Fields in SQLVAR

Usage in DESCRIBE and

Usage in EXECUTE, FETCH, OPEN,

Extended DESCRIBE (set

PUT, and extended dynamic statements

Assembler or

by the database manager

of the same name (set by the user prior

PL/I Name

C Name

Data Type

except for SQLN)

to executing the statement)

SQLTYPE

sqltype

SMALLINT

Indicates the data type of

Indicates the data type of the host

the column and whether it

variable and whether an indicator

can contain nulls. For a

variable is provided. For a description of

description of the type

the type codes, see Table 22 on page 362.

codes, see Table 22 on page

362.

360

SQL Reference

SQLDA

Table 21. Fields in SQLVAR

(continued)

Usage in DESCRIBE and

Usage in EXECUTE, FETCH, OPEN,

Extended DESCRIBE (set

PUT, and extended dynamic statements

Assembler or

by the database manager

of the same name (set by the user prior

PL/I Name

C Name

Data Type

except for SQLN)

to executing the statement)

SQLLEN

sqllen

SMALLINT

The length attribute of the

The length attribute of the host variable.

column. For datetime

See Table 22 on page 362.

columns, the length of the

string representation of the

values. See Table 22 on

page 362.

SQLDATA

sqldata

pointer

For string columns,

A pointer to the storage area that either

SQLDATA contains the

holds the parameter value (if SQLDA is

CCSID of the column. For

used for input), or is to hold a select list

character string columns,

result (if the SQLDA is used for output).

SQLDATA can alternatively

For varying-length character strings, the

contain the value X'FFFF'

actual data should be preceded by a

indicating bit data. For

halfword field that specifies the length

datetime columns, the

of the character string. (The value

SQLDATA contains the

should not include the length of the

CCSID of the string

halfword.) The data must be aligned on

representation of the

a halfword boundary.

values. See Table 23 on

page 363 for more

information.

SQLIND

sqlind

pointer

For character and datetime

Contains the address of the indicator

data, byte 1 of SQLIND is

variable where applicable. The indicator

set as follows:

variable must be declared as a 15-bit

v X'FF' for a bit value

integer.

v X'01' for a SBCS value

v X'02' for a mixed value

For an Input SQLDA, the indicator

should be set to 0 to indicate that the

This information is not

parameter value is not null and to a

available when using the

negative value to indicate that the

DRDA protocol.

parameter value is null.

For an Output SQLDA, the database

manager fills in the indicator using the

following rules:

Denotes that the parameter is

not null, and is in the

associated storage area.

Denotes that the parameter

value is null.

Denotes that a returned value

was truncated because the

storage area provided was not

large enough. If the truncated

item was a DBCS or character

string, the indicator variable

contains the length in characters

before truncation. (Applies only

for the FETCH statement.)

When a time value is truncated

at its seconds part on output,

the seconds are placed in the

SQLIND.

Appendix B. SQLCA and SQLDA

361

SQLDA

Table 21. Fields in SQLVAR

(continued)

Usage in DESCRIBE and

Usage in EXECUTE, FETCH, OPEN,

Extended DESCRIBE (set

PUT, and extended dynamic statements

Assembler or

by the database manager

of the same name (set by the user prior

PL/I Name

C Name

Data Type

except for SQLN)

to executing the statement)

SQLNAME

sqlname

VARCHAR

Contains the name or label

For character, datetime and graphic data,

(30)

associated with the column

SQLNAME may be used to override¹

used in the select list of the

the default CCSID. An override is

DESCRIBE statement. For

indicated by the following:

more information, see

v the length of the SQLNAME field is 8

“SQLNAME” on page 248.

v the first 2 bytes of SQLNAME have a

value of X'0000'

v for SQLDS protocol, the sixth position

of the SQLAID field must be '+'; for

example, 'SQLDA+ ' (see SQLDAID in

Table 20 on page 360).

The override itself is present in bytes 3

and 4 of the SQLNAME. See Table 23 on

page 363 for the relation between the

character subtypes, the graphic data

type, and the CCSID.

Note that bytes 5 to 8 of the SQLNAME

field are reserved by IBM for future use

in override situations for character and

graphic data.

For all other data, SQLNAME is not

used.

It is important to note that this use of the SQLNAME field is only for overrides. Applications that use the

defaults and have properly initialized SQLDAs need not be concerned.

Note: Note that in a remote unit of work application a DESCRIBE of a SELECT statement will return the

application server’s CCSIDs and that these will not necessarily be the same as any host variables that may be in the

select list (these will have CCSIDs from the application requester).

SQLTYPE and SQLLEN

The following table shows the values that may appear in the SQLTYPE and

SQLLEN fields of the SQLDA. In DESCRIBE, an even value of SQLTYPE means

the column does not allow nulls, and an odd value means the column does allow

nulls. In EXECUTE, FETCH, OPEN, and PUT, an even value of SQLTYPE means

no indicator variable is provided, and an odd value means that SQLIND contains

the address of an indicator variable.

Table 22. SQLTYPE and SQLLEN Values for DESCRIBE, EXECUTE, FETCH, OPEN, and PUT

For DESCRIBE

For EXECUTE, FETCH, OPEN, and PUT

HOST VARIABLE DATA

SQLTYPE

COLUMN DATA TYPE

SQLLEN

TYPE

SQLLEN

384/385

date

10 or length of

fixed-length character

length attribute of

LOCAL date

string representation of a

the host variable

format

date

388/389

time¹

8 or length of

fixed-length character

length attribute of

LOCAL time

string representation of a

the host variable

format

time

362

SQL Reference

SQLDA

Table 22. SQLTYPE and SQLLEN Values for DESCRIBE, EXECUTE, FETCH, OPEN, and PUT (continued)

For DESCRIBE

For EXECUTE, FETCH, OPEN, and PUT

HOST VARIABLE DATA

SQLTYPE

COLUMN DATA TYPE

SQLLEN

TYPE

SQLLEN

392/393

timestamp¹

fixed-length character

length attribute of

string representation of a

the host variable

timestamp

448/449

varying-length character

length attribute of

varying-length character

length attribute of

string

the column

string

the host variable

452/453

fixed-length character

length attribute of

fixed-length character

length attribute of

string

the column

string

the host variable

456/457

long varying-length

length attribute of

long varying-length

length attribute of

character string

the column

character

string

the host variable

460/461

N/A

NUL-terminated character

length attribute of

string

the host variable

464/465

varying-length graphic

length attribute of

varying-length graphic

length attribute of

string

the column

string

the host variable

468/469

fixed-length graphic string

length attribute of

fixed-length graphic string

length attribute of

the column

the host variable

472/473

long varying-length

length attribute of

long varying-length

length attribute of

graphic string

the column

graphic

string

the host variable

480/481

floating point

4 for single

floating point

4 for single

precision, 8 for

double precision

484/485

packed decimal

precision in byte

packed decimal

precision in byte

1; scale in byte 2

488/489

zoned decimal²

precision in byte

zoned decimal²

precision in byte

1; scale in byte 2

496/497

large integer

500/501

small integer

504/505

N/A

DISPLAY SIGN LEADING

precision in byte

SEPARATE

1; scale in byte 2

Since host variables do not have datetime data types, character string variables must be used to retrieve

datetime values. Thus, when the SQLDA describes host variables, these type-codes denote fixed-length

character string variables.

Zoned decimal is not supported for local operations.

CCSID Usage

The following table describes the SQLDATA field for the DESCRIBE statement and

the SQLNAME field for host variables.

Table 23. CCSID Values for SQLDATA and SQLNAME

Data Type

Subtype

Bytes 1 & 2

Bytes 3 & 4

Character

SBCS data

X'0000'

The CCSID value

Character

mixed data

X'0000'

The CCSID value

Datetime

SBCS data

X'0000'

The CCSID value

Datetime

mixed data

X'0000'

The CCSID value

Appendix B. SQLCA and SQLDA

363

SQLDA

Table 23. CCSID Values for SQLDATA and SQLNAME (continued)

Data Type

Subtype

Bytes 1 & 2

Bytes 3 & 4

Character

bit data

X'0000'

X'FFFF'

Graphic

N/A

X'0000'

The CCSID value

Any other data type

N/A

INCLUDE SQLDA Declarations

The description of the SQLDA that is given by INCLUDE SQLDA is shown for

assembler, PL/I and C. Though you can use an SQLDA in VS COBOL II, and

Fortran, the INCLUDE statement does not provide the code; you must provide it, as

shown in the DB2 Server for VSE & VM Application Programming manual.

Assembler

SQLDA

DSECT

SQLDAID

CL8

SQLDABC

SQLN

SQLD

SQLVAR

SQLVARN

DSECT

SQLTYPE

SQLLEN

SQLPRCSN DS

CL1

SQLSCALE DS

CL1

SQLDATA DS

SQLIND DS

SQLNAME DS

H,CL30

&SYSECT CSECT

#ifndef SQLDASIZE

struct sqlda {

unsigned char sqldaid[8];

long sqldabc;

short sqln;

short sqld;

struct sqlvar {

short sqltype;

short sqllen;

unsigned char *sqldata;

short *sqlind;

struct sqlname {

short length;

char data[30];

} sqlname;

} sqlvar[1];

};

#define SQLDASIZE(n)

(sizeof(struct sqlda)+((n)-1)*sizeof(struct sqlvar))

#endif

Note: SQLDA character array variables sqldaid and sqlname.data are not

NUL-terminated. They cannot be directly used by C string manipulation

functions.

The SQLDA must not be declared within the SQL declare section.

364

SQL Reference

SQLDA

Using the defined preprocessor function SQLDASIZE, your program can

dynamically allocate an SQLDA of adequate size for use with each EXECUTE

statement. For example, the code fragment below allocates an SQLDA adequate for

five fields and uses it in an EXECUTE statement S3:

struct sqlda *sqlptr;

sqlptr = (struct sqlda *)malloc(SQLDASIZE(5));

sqlptr->SQLN=5;

/* Add code to set the rest of values and pointers in the SQLDA */

EXEC SQL EXECUTE S3 USING DESCRIPTOR *sqlptr;

Note: The variable used to point to the SQLDA is not defined in a SQL declare

section. Its context within an SQL statement (following INTO or USING

DESCRIPTOR) is enough to identify it.

You can use a similar technique to allocate an SQLDA for use with a DESCRIBE

statement. The following program fragment illustrates the use of SQLDA with

DESCRIBE for three fields and a 'prepared' statement S1:

struct sqlda *sqlptr;

EXEC SQL DECLARE C1 CURSOR FOR S1;

sqlptr = (struct sqlda *)malloc(SQLDASIZE(3));

sqlptr->sqln=5;

EXEC SQL DESCRIBE S1 INTO *sqlptr;

if (sqlptr->sqld > sqlptr->sqln)

--get a bigger one

Set sqldata and sqlind

EXEC SQL OPEN C1;

EXEC SQL FETCH C1 USING DESCRIPTOR *sqlptr;

There is no standard C to support packed decimal data. If data in packed decimal

format is required, the SQLDA must be filled in with an SQLTYPE of 484 or 485,

with the appropriate values for precision and scale in SQLLEN. The C program

would then deal with the data in its packed format.

PL/I

DCL 1 SQLDA BASED(SQLDAPTR),

2 SQLDAID

CHAR(8),

2 SQLDABC

BIN FIXED(31),

2 SQLN

BIN FIXED(15),

2 SQLD

BIN FIXED(15),

2 SQLVAR

(SQLSIZE REFER(SQLN)),

3 SQLTYPE BIN FIXED(15),

3 SQLLEN

BIN FIXED(15),

3 SQLDATA

PTR,

3 SQLIND

PTR,

3 SQLNAME

CHAR(30) VAR;

DCL SQLSIZE BIN FIXED(15);

DCL SQLDAPTR PTR;

The SQLDA must not be declared within the SQL declare section.

In addition to the structure above, you should also declare an additional mapping

for the same area. The SQLPRCSN and SQLSCALE fields of the second mapping

are used when decimal data is used. An example of this mapping follows.

DCL 1 SQLDA BASED(SQLDAPTR),

2 SQLDAIDX

CHAR(8),

2 SQLDABCX

BIN FIXED(31),

2 SQLNX

BIN FIXED(15),

2 SQLDX

BIN FIXED(15),

2 SQLVARX(SQLSIZE REFER(SQLNX)),

Appendix B. SQLCA and SQLDA

365

SQLDA

3 SQLTYPEX BIN FIXED(15),

3 SQLPRCSN format 1 or format 2

3 SQLSCALE format 1 or format 2

3 SQLDATAX PTR,

3 SQLINDX PTR,

3 SQLNAMEX CHAR(30) VAR:

You can declare the SQLPRCSR and SQLSCALE fields in one of two formats:

Format 1

3 SQLPRCSN BIT(8),

3 SQLSCALE BIT(8),

The fields must be set by 8-bit strings. For example, for a precision of 5 and a scale

of 2, the following assignments are required:

SQLDAPTR->SQLPRCSN = ’00000101’B,

SQLDAPTR->SQLSCALE = ’00000010’B,

Format 2

3 SQLPRCSN CHAR(1),

3 SQLSCALE CHAR(1),

This format requires the declaration of additional variables. These are a CHAR(2)

variable and a BASED FIXED BIN (15) variable for both precision and scale. For

example:

DCL PRCSNC CHAR(2)

DCL PRCSNN FIXED BIN(15) BASED (ADDR(PRCSNC));

DCL SCALEC CHAR(2);

DCL SCALEN FIXED BIN(15) BASED (ADDR(SCALEC));

For a precision of 5 and a scale of 2, the following assignments are required:

PRCSNN = 5;

SCALEN = 2;

The SQLDAX fields for a precision of 5 and a scale of 2 would be:

SQLDAPTR->SQLPRCSN = SUBSTR(PRCSNC,2,1);

SQLDAPTR->SQLSCALE = SUBSTR(SCALEC,2,1);

This format, though more complex than Format 1, allows PL/I manipulation of the

precision and scale fields. For example, the value of the SQLPRCSN field can be

determined by simply reversing the substring operation above. That is:

SUBSTR(PRCSNC,2,1) = SQLDAPTR->SQLPRCSN;

Such an operation is not possible using Format 1.

Because the PL/I SQLDA is declared as a based structure, your program can

dynamically allocate an SQLDA of adequate size with each EXECUTE statement.

For example, the code fragment below allocates an SQLDA adequate for five fields

and uses it to EXECUTE statement S3:

SQLSIZE=5;

ALLOCATE SQLDA SET(SQLDAPTR);

/*Add code to set values and pointers in the SQLDA*/

EXEC SQL EXECUTE S3 USING DESCRIPTOR SQLDA;

The statement SQLSIZE=5 determines the size of the SQLDA to be allocated by

means of the PL/I REFER feature. The ALLOCATE statement allocates an SQLDA

366

SQL Reference

Appendix C. DB2 Server for VSE & VM Catalog

This appendix is intended to help you to use the catalog for your database

manager. It contains Product-Sensitive Programming Interface and Associated

Guidance Information.

The DB2 Server for VSE & VM database manager automatically maintains

information about the database in a set of tables called the catalog. The catalog

tables are created by the database manager during database generation. They

describe tables, columns, indexes, keys, packages, authorities, and other objects in

the database. Data in the catalog tables is available to authorized users through

normal SQL query facilities; however, the catalog is primarily intended for use by

the database manager.

During database generation, the catalog is defined as normal tables with PUBLIC

read authorization. After database generation, a user with DBA authority can

revoke the select privilege from PUBLIC. Usually all users are allowed to access

the catalog, so you can use SQL statements to retrieve information in the catalog.

For example, this SQL statement finds what column names in table SALARY begin

with the letter ‘D’:

SELECT CNAME FROM SYSTEM.SYSCOLUMNS

WHERE TNAME = ’SALARY’

AND CNAME LIKE ’D%’

SYSTEM is the owner of all catalog tables except SYSLANGUAGE (which is

owned by SQLDBA). You must qualify all references to catalog tables with the

owner name, unless you have a synonym defined.

After database generation, the only information in the tables not available to

everyone is password information. You must have DBA authority to access the

catalog table that contains passwords (SYSUSERAUTH). A view, called

SYSUSERLIST, is defined on SYSUSERAUTH when the catalog tables are created.

The owner of the view is SQLDBA, so you must refer to the view as

SQLDBA.SYSUSERLIST. This view is accessible to all users and contains all the

columns of SYSUSERAUTH except the passwords. If you do not have DBA

authority, you must query the view (SYSUSERLIST) instead of the underlying table

(SYSUSERAUTH).

Some of the information in the catalog is of little interest to most users. Statistics

maintained in the catalog, for example, are used by the database manager to

determine optimal access paths. These statistics may be quite meaningless to you.

If you wish, you can define views on the catalog tables containing only columns

that are meaningful to you.

Some of the information in the catalog is maintained in a form for internal use by

the database manager and is provided as additional guidance on database

administration tasks. Two special data types are used: DBAINT and DBAHW.

These appear externally like INTEGER and SMALLINT data. However, DBAINT

and DBAHW do not sort as expected when they contain negative values.

Consequently, queries that use ORDER BY, GROUP BY, or predicates that involve >

or < operations on these values may not work as expected.

369

The database manager updates its catalog during normal operation in response to

SQL data definition and control statements. It also updates its catalog when

programs are preprocessed.

You can create and maintain your own installation-dependent catalog tables using

SQL statements.

Note: Data in the catalog tables is available to authorized users through normal

SQL query facilities; however, the catalog is primarily intended for use by

the database manager, and is therefore subject to change.

“Roadmap” to Catalog

Item

Catalog Table

authorization

SYSUSERAUTH

406

SYSUSERLIST

character conversion

SYSSTRINGS

400

character set

SYSCHARSETS

378

coded character set identifiers

SYSCCSIDS

378

SYSSTRINGS

400

column

SYSCOLUMNS

381

SYSKEYCOLS

390

column update privilege

SYSCOLAUTH

379

column with field procedure

SYSFIELDS

386

constraint

SYSKEYS

391

dbspace

SYSDBSPACES

384

SYSUSAGE

405

SYSDROP

385

dbspace waiting to be dropped

SYSDROP

385

default

SYSOPTIONS

393

dropped dbspace

SYSDROP

385

dropped table

SYSDROP

385

field procedures

SYSFPARMS

387

SYSFIELDS

386

foreign key

SYSKEYS

391

index

SYSINDEXES

388

SYSUSAGE

405

index column statistics

SYSCOLSTATS

380

SYSCOLUMNS

381

SYSINDEXES

388

key

SYSKEYS

391

key column

SYSKEYCOLS

390

language for character set

SYSLANGUAGE

392

option

SYSOPTIONS

393

package

SYSACCESS

373

SYSUSAGE

405

package run privilege

SYSPROGAUTH

396

password

SYSUSERAUTH

406

370

SQL Reference

Roadmap

Item

Catalog Table

privilege

SYSCOLAUTH

379

SYSPROGAUTH

396

SYSTABAUTH

403

primary key

SYSKEYS

391

statistics

SYSCATALOG

375

SYSCOLSTATS

380

SYSCOLUMNS

381

SYSDBSPACES

384

SYSINDEXES

388

synonym

SYSSYNONYMS

402

stored procedures

SYSPARMS

395

SYSROUTINES

398

SYSPSERVERS

397

table

SYSCATALOG

375

SYSCOLUMNS

381

SYSUSAGE

405

table privilege

SYSTABAUTH

403

table waiting to be dropped

SYSDROP

385

unique constraint

SYSKEYS

391

view

SYSVIEWS

406

SYSCATALOG

375

SYSCOLUMNS

381

SYSACCESS

373

SYSUSAGE

405

view privilege

SYSTABAUTH

403

Updateable Columns

Only someone with DBA authority may enter UPDATE, INSERT and DELETE

statements against catalog tables. Furthermore, only the following columns may be

altered. It is not possible to add columns to the catalog.

Catalog Column

Update

Insert

Delete

SYSACCESS

VALID¹

SYSCATALOG

CLUSTERTYPE

CLUSTERROW

AVGROWLEN

ROWCOUNT

NPAGES

PCTPAGES

SYSCCSIDS

all columns

SYSCHARSETS

all columns

Appendix C. DB2 Server for VSE & VM Catalog

371

Updateable Columns

2. Updating this field is only effective if the corresponding CCSID field value for

the row is null. Upon successful update of the SUBTYPE value, all packages

which reference the column whose SUBTYPE has been updated must be

re-preprocessed.

3. CAUTION:

Changing NPAGES makes the dbspace appear to be a different size without

actually changing it. NPAGES should not be changed in a production

environment, to do so may cause errors to occur. It is intended for testing

purposes only.

SYSACCESS

Packages are stored in tables. The database manager uses SYSACCESS to record

information about the tables in which packages are stored. For package tables that

are in use, SYSACCESS records information about:

v Packages created by the preprocessors or by a CREATE PACKAGE statement.

v View definitions (views are stored as packages).

When a package table is not in use, SYSACCESS indicates whether the table is

available or unavailable.

The columns in SYSACCESS are:

Column Name

Data Type

Description and Comments

TNAME

VARCHAR(18)

When the package table is in use, TNAME is either the name of the

NOT NULL

package or the name of a view. A view definition is stored as a package;

the name of the package is the name of the view. The TABTYPE field

indicates whether this row describes a real package or a view.

When the package table is unused, TNAME is either ‘!0x AVAILABLE’

or ‘¢0x UNAVAILABLE’ to indicate whether the table is available or

unavailable. A package table is available when it is unused and the

DBSPACE is not full. A package table is unavailable when it is unused

and the DBSPACE is full. A package table may also be marked as

unavailable when a package is dropped from a DBSPACE that was

previously marked full. Such package tables are marked as available the

next time the database manager pre-allocates packages. The x is a

number from one to five that is used internally.

CREATOR

CHAR(8)

The owner of the package or view who either preprocessed the program

NOT NULL

associated with this package, explicitly created the package (by CREATE

PACKAGE), or created this view.

If the package table is unused, CREATOR is a non-readable unique value

that is based on the system clock. (The database manager generates this

value for unused package tables because TNAME and CREATOR serve

as a key for an index on SYSTEM.SYSACCESS.)

DBSPACENO

DBAHW

The number of the DBSPACE that contains this package. (When

NOT NULL

DBSPACEs are defined by database generation or by ADD DBSPACE

processing, the database manager assigns each DBSPACE a number for

internal use.)

TABID

DBAHW

Packages are stored as tables. TABID contains the internal identifier of

NOT NULL

that table. (In the DB2 Server for VSE & VM Diagnosis Guide and Reference

manual, this identifier is known as the DBSS RID.)

Appendix C. DB2 Server for VSE & VM Catalog

373

SYSACCESS

Column Name

Data Type

Description and Comments

LINKID

DBAHW

A package may occupy more than one row of the table in which it is

NOT NULL

stored. The database manager connects these rows in the correct order by

a mechanism called a unary link. LINKID is the identifier of that unary

link.

FIRSTROW

DBAINT

The internal identifier for the first row of the unary link. (In the DB2

NOT NULL

Server for VSE & VM Diagnosis Guide and Reference manual, row identifiers

are known as DBSS TIDs.)

FIRSTROW is 0 for unused package tables.

TIMESTAMP

CHAR(17)

The date and time when this package was created. The field has the

NOT NULL

format MM/DD/YY HH:MM:SS. It is updated when the database

manager automatically preprocesses the package. (The database manager

attempts to preprocess a package when some dependency is lost; for

example, when a package tries to use an index that was dropped.)

TIMESTAMP is blank if the package table is unused.

VALID

CHAR(1)

The possible values are:

NOT NULL

if the package is valid.

if the package is not valid because:

v a view, index, table, or DBSPACE has been dropped

v the application server CHARNAME has been

changed and the package or view definition has a

dependency on a changed system table. In this case,

please refer to the DB2 Server for VM System

Administration or the DB2 Server for VSE System

Administration manual for a list of all affected tables.

blank

if the package table is unused.

TABTYPE

CHAR(1)

The possible values are:

NOT NULL

if this row describes a package.

if this row describes a view definition.

blank

if the package table is unused.

CONSTKN

CHAR(8)

The consistency token for this package. The field is one of:

FOR BIT DATA

eight blanks

If this entry is for a view, the view was created or

repreprocessed on an SQL/DS database Version 3

Release 1 or later. If not for a view, CTOKEN(NO) was

specified or allowed to default in the preprocessor

options.

timestamp

CTOKEN(YES) was specified in the preprocessor

options.

null

The package or view was migrated from an SQL/DS

database prior to Version 3 Release 1.

374

SQL Reference

SYSACCESS

Column Name

Data Type

Description and Comments

PLABEL

VARCHAR(30)

The label for this package. The field is one of:

thirty blanks

If this entry is for a view, the view was created or

repreprocessed on an SQL/DS database Version 3

Release 1 or later. If for a package, the LABEL option

was not specified in either the preprocessor options or

in a CREATE PACKAGE statement.

label-text

LABEL(label-text) was specified in the preprocessor

options or for CREATE PACKAGE.

null

The package or view was migrated from an SQL/DS

database prior to Version 3 Release 1.

SYSCATALOG

The SYSCATALOG table contains a row for each table or view in the database,

including itself and other catalog tables.

The columns in SYSCATALOG are:

Column Name

Data Type

Description and Comments

TNAME

VARCHAR(18)

The name of the table or view being described.

NOT NULL

CREATOR

CHAR(8)

The owner of the table or view. (The CREATOR of the catalog is

NOT NULL

SYSTEM.)

TABLETYPE

CHAR(1)

The possible values are:

NOT NULL

if the object is a view.

if the object is a real table.

NCOLS

SMALLINT

The number of columns in the table or view.

NOT NULL

REMARKS

VARCHAR(254)

The information from a COMMENT statement entered for the table or

NOT NULL

view. The remarks are deleted from SYSCATALOG when the table or

view is dropped.

If the DBCS option is enabled, users can store mixed data (EBCDIC and

DBCS) in the REMARKS column.

DBSPACENO

DBAHW

The possible values are:

NOT NULL

if the object is a view.

number

if the object is a real table. This number is the internal number

of the DBSPACE in which the table is stored. This is the

DBSPACE number to which some of the SHOW operator

commands refer (such as SHOW DBSPACE).

DBSPACENAME

VARCHAR(18)

The name of the DBSPACE containing the table.

NOT NULL

Appendix C. DB2 Server for VSE & VM Catalog

375

SYSCATALOG

Column Name

Data Type

Description and Comments

TABID

DBAHW

The possible values are:

NOT NULL

if the object is a view.

number

if the object is a real table. This number is the internal identifier

of the table. (In the DB2 Server for VSE & VM Diagnosis Guide

and Reference manual, the internal identifier is referred to as the

DBSS RID.)

CLUSTERTYPE

CHAR(1)

The possible values are:

NOT NULL

if the rows are clustered by an index.

if the rows are clustered by default rules.

This is its initial value. Internally, the physical placement of rows is

determined by an index or (if no index is available) by default rules. The

default rules place each new row near the previously inserted row.

CLUSTERTYPE is updated by CREATE and DROP INDEX statement on

this table.

In addition to the above values, the possible values for catalog tables are:

if the rows are clustered by link rules. For certain catalog tables,

a direct addressing link is set up to enable faster access to a

specific row. A CLUSTERTYPE value of 'L' indicates that rows

are clustered in link order.

if the rows are clustered by internal RDS rules.

CLUSTERROW

DBAINT

The possible values are:

NOT NULL

if the object is a view. This is also an initial value.

number

if the object is a real table. This number is the highest internal

row identifier (DBSS TID) for any row in the table. The database

manager uses this value when it is clustering rows by default

rules.

See Note 1 for update rules on this column.

AVGROWLEN

DBAHW

The average length of the rows in this table, rounded to the nearest

NOT NULL

integer. This field is set to -1 when the table is created. See Note 1 for

update rules on this column.

ROWCOUNT

DBAINT

The total number of rows in this table. This is updated to the following

NOT NULL

values by CREATE TABLE, UPDATE STATISTICS, and

DATALOAD/RELOAD as indicated:

-2

When a DATALOAD with COMMITCOUNT option reaches the

commit threshold and commits the loaded rows if statistics are

collected while data is being loaded.

-1

When the table is initially created with CREATE TABLE.

>=0

When an UPDATE STATISTICS is performed, or when a

CREATE/REORGANIZE INDEX is performed, or when data is

loaded using DATALOAD/RELOAD (and update statistics is

not set off). The integer is equal to the total number of rows in

this table and it is updated only if one of the previously listed

operations is performed.

376

SQL Reference

SYSCATALOG

Column Name

Data Type

Description and Comments

NPAGES

DBAINT

The number of pages on which rows of this table appear. This number is

NOT NULL

approximate because it does not contain those pages that contain only

long fields. Thus, the sum of the NPAGES for all tables in a DBSPACE

might be less than NACTIVE in SYSDBSPACES. (NACTIVE is the total

number of active data pages in a DBSPACE.)

This field is set to -1 when the table is created, and is updated to a

non-negative integer according to the same rules as the ROWCOUNT

column.

PCTPAGES

DBAHW

The approximate percentage of the total active pages in the DBSPACE

NOT NULL

that have rows from this table on them. The initial value in this field is

-1. Loading, updating or dropping of any table can affect the actual

percentage of used pages for all tables in a given DBSPACE and

PCTPAGES may not reflect this. The database manager takes this into

account and dynamically calculates (but does not update) PCTPAGES

whenever it is used. The calculated value is NPAGES/

SYSDBSPACES.NACTIVE. See Note 1 for update rules on this column.

NOVERFLOW

DBAINT

The number of rows in this table that have overflowed from their

NOT NULL

original page in storage to another page. If this number is large, it may

be time to reorganize the table by dumping it out of the database and

reloading it. See Note 1 for update rules on this column.

LFDTABID

DBAHW

The internal table identification (referred to in the DB2 Server for VSE &

NOT NULL

VM Diagnosis Guide and Reference manual as the DBSS RID) of a

secondary table the database manager uses to store any long fields that

exist in this table. The secondary table is transparent to users.

This field is zero if the described table has no long fields.

LFDLINK

DBAHW

The rows in the secondary table that contains long field data are linked

NOT NULL

together by an internal mechanism called a unary link. LFDLINK is the

identifier of that unary link. (In the DB2 Server for VSE & VM Diagnosis

Guide and Reference manual, the internal identifier is referred to as a DBSS

LID.) If the described table contains no long fields, LFDLINK is zero.

LFDDBSPACE

DBAHW

The number of the DBSPACE that contains the long field data table.

NOT NULL

LFDDBSPACE is zero if there are no long fields in the described table.

TLABEL

VARCHAR(30)

A table label supplied by a user using a LABEL statement. The table

labels are deleted from SYSCATALOG when the table or view is

dropped.

If the DBCS option is enabled, users can store mixed data (EBCDIC and

DBCS) in the TLABEL column.

PARENTS

SMALLINT

The number of parent relationships in which the table is a dependent.

Can be NULL if migrated from SQL/DS Version 2 Release 1 or earlier.

DEPENDENTS

SMALLINT

The number of dependent relationships in which the table is a parent.

Can be NULL if migrated from SQL/DS Version 2 Release 1 or earlier.

INACTIVE

SMALLINT

The number of inactive keys for the table. This includes inactive primary

keys, inactive foreign keys, and foreign keys that reference an inactive

primary key in another table.

Can be NULL if migrated from SQL/DS Version 2 Release 1 or earlier.

Appendix C. DB2 Server for VSE & VM Catalog

377

SYSCATALOG

Column Name

Data Type

Description and Comments

DATACAPTURE

CHAR(1)

Records the value of the DATA CAPTURE specification for a table. This

value can be NULL if the database was migrated from Version 3

Release 5 or earlier, 'blank' if DATA CAPTURE NONE was specified for

the table, or Y if DATA CAPTURE CHANGES was specified.

Note 1:

The value is always updated by an UPDATE STATISTICS

statement on this table or by the DATALOAD/RELOAD DBS

Utility commands if the collecting of statistics has not been turned

off by SET UPDATE STATISTICS OFF.

SYSCCSIDS

The SYSCCSIDS table contains a row for every CCSID supported by the

installation.

Column Name

Data Type

Description and Comments

CCSID

INTEGER

Identifies the CCSIDs supported by the installation. The values in this

NOT NULL

field identify valid CCSIDs when columns are created by the CREATE

TABLE or ALTER TABLE statement. This column is defined with a

UNIQUE constraint.

SUBTYPE

CHAR(1)

Identifies the subtype of the CCSID. The possible values are:

NOT NULL

for bit data.

M for mixed data.

for SBCS data.

blank for anything other than non-character.

SBCSID

INTEGER

Identifies the SBCS portion of a mixed CCSID.

NOT NULL

DBCSID

INTEGER

Identifies the DBCS portion of a mixed CCSID.

NOT NULL

CHARNAME

CHAR(18)

The name of the character set specified by the SQLINIT EXEC (for

NOT NULL

example, FRENCH, INTERNATIONAL, 937).

More information on CCSIDs can be found in the DB2 Server for VM System

Administration or the DB2 Server for VSE System Administration manual.

SYSCHARSETS

The rows in SYSCHARSETS contain information about various EBCDIC character

sets. The database manager reads a row from this table during initialization based

on the name specified by the CHARNAME parameter of the SQLSTART command.

The database manager uses the character sets to identify valid characters, to fold

lowercase characters to uppercase properly, and for the TRANSLATE function.

IBM supplies sample DBS Utility control files that you can use for loading

character set information into SYSCHARSETS. Or, you can define your own

character sets and have them loaded by someone with DBA authority. For more

information on how to define your own character set, see the DB2 Server for VM

System Administration or the DB2 Server for VSE System Administration manual.

SYSCHARSETS is only for SBCS character sets.

378

SQL Reference

SYSCHARSETS

Column Name

Data Type

Description and Comments

NAME

VARCHAR(18)

The name used to identify the character set. NAME is usually the

NOT NULL

national language name of the character set (for example, FRENCH) and

corresponds exactly to the CHARNAME in the SYSCCSIDS catalog table.

A CCSID is associated with each name.

CHARCLASS

CHAR(192)

This contains the character classifications for this character set.

NOT NULL

FOR BIT DATA

CHARTRANS

CHAR(192)

This contains the character translation values for this character set. The

NOT NULL

character translation values are used for lowercase to uppercase folding.

FOR BIT DATA

SYSCOLAUTH

SYSCOLAUTH records grants of the UPDATE privilege on tables and views when

the privilege is granted on a column-by-column basis. Each entry in

SYSCOLAUTH has a corresponding entry in SYSTABAUTH with a matching

timestamp. (SYSTABAUTH records privileges granted on entire tables, but not on

individual columns.) A SYSCOLAUTH entry identifies a particular column on

which an UPDATE privilege has been granted. For example, if the UPDATE

privilege is granted on several columns in one GRANT statement, the grant is

represented as one entry in SYSTABAUTH, and several entries in SYSCOLAUTH,

all having matching timestamps.

Some of the entries in SYSCOLAUTH represent privileges that are exercised by

preprocessed programs. These entries appear as though the creator of the program

(the user who preprocessed the program) granted the privilege to the program

itself. The columns in SYSCOLAUTH are:

Column Name

Data Type

Description and Comments

GRANTOR

CHAR(8) NOT

The user ID of the person who granted the UPDATE privilege on this

NULL

column.

GRANTEE

CHAR(8)

The user ID of the person who holds the UPDATE privilege. If the userid

NOT NULL

is PUBLIC, the privilege is held by all users.

CREATOR

CHAR(8)

The owner of the table that contains the column.

NOT NULL

TNAME

VARCHAR(18)

The name of the table that contains the column. (CREATOR.TNAME

NOT NULL

uniquely identifies the table that contains the column.)

TIMESTAMP

CHAR(12)

The value of the System/390 time of day clock when the grant was

NOT NULL

made. This value is used internally when privileges are revoked, and is

stored as a string of numbers and letters.

COLNAME

VARCHAR(18)

The name of the column on which the UPDATE privilege has been

NOT NULL

granted.

Note: The authorization for update by column appears in a separate table, one

column per row, only because it is possible to grant the UPDATE privilege

on specific columns of the table. If the user has the UPDATE privilege on all

Appendix C. DB2 Server for VSE & VM Catalog

379

SYSCOLAUTH

columns of a table, that information does not appear in SYSCOLAUTH;

rather, UPDATECOLS in SYSTABAUTH is set to ‘ ’. Otherwise,

UPDATECOLS contains ‘*’ to indicate that more information is in the

SYSCOLAUTH table.)

SYSCOLSTATS

The SYSCOLSTATS table keeps the column statistics listed below for a column

which is the first column of an index. SYSCOLSTATS is updated whenever an

index is created, reorganized or dropped, or UPDATE STATISTICS is run. The

statistics in SYSCOLSTATS are used internally by the database manager.

Because SYSCOLSTATS records the first and second-most frequent values in the

first column used by every index on every table in the database, you should

consider revoking public access to SYSCOLSTATS if any of these values could be

sensitive data.

The columns in SYSCOLSTATS are:

Column Name

Data Type

Description and Comments

CNAME

VARCHAR(18)

The name of the column described.

NOT NULL

TNAME

VARCHAR(18)

The name of the table in which the column (CNAME) is located.

NOT NULL

CREATOR

CHAR(8)

The owner who created the table identified by TNAME. (Thus, CNAME

NOT NULL

is the name of a column in the table identified by CREATOR.TNAME.)

VAL10

VARCHAR(12)

The value of column CNAME at the tenth percentile. If the table TNAME

NOT NULL

has N rows and all N values of CNAME are arranged in ascending order,

FOR BIT DATA

then VAL10 is at position 0.1 * N in this sequence.

VAL50

VARCHAR(12)

The value of column CNAME at the 50th percentile. If the table TNAME

NOT NULL

has N rows and all N values of CNAME are arranged in ascending order,

FOR BIT DATA

then VAL50 is at position 0.5 * N in this sequence.

VAL90

VARCHAR(12)

The value of column CNAME at the 90th percentile. If the table TNAME

NOT NULL

has N rows and all N values of CNAME are arranged in ascending order,

FOR BIT DATA

then VAL90 is at position 0.9 * N in this sequence.

FREQ1VAL

VARCHAR(12)

The most frequent value in the column. If there is more than one value,

NOT NULL

the smaller is used. If the column is not a character data type,

FOR BIT DATA

FREQ1VAL may be unprintable.

FREQ1PCT

SMALLINT

The percent frequency of FREQ1VAL

NOT NULL

FREQ2VAL

VARCHAR(12)

The second most frequent value in the column. If there is more than one

NOT NULL

value, the smaller is used. If the column is not a character data type,

FOR BIT DATA

FREQ2VAL may be unprintable.

FREQ2PCT

SMALLINT

The percent frequency of FREQ2VAL

NOT NULL

380

SQL Reference

SYSCOLUMNS

The SYSCOLUMNS table contains a more detailed description of the database than

that contained in SYSCATALOG. Recall that SYSCATALOG contains a row for each

table or view in the database; SYSCOLUMNS contains a row for every column of

every table or view in the database (including the columns of the catalog tables).

The columns in SYSCOLUMNS are:

Column Name

Data Type

Description and Comments

CNAME

VARCHAR(18)

The name of the column described.

NOT NULL

TNAME

VARCHAR(18)

The name of the table or view in which the column (CNAME) is located.

NOT NULL

CREATOR

CHAR(8)

The owner of the table or view identified by TNAME. (Thus, CNAME is

NOT NULL

the name of a column in the table or view identified by

CREATOR.TNAME.)

COLNO

SMALLINT

The number of the column in the table. The value in COLNO

NOT NULL

corresponds to the sequence that columns are specified in the CREATE

TABLE statement or added in the ALTER TABLE statement.

COLTYPE

CHAR(8)

The data type of the column: INTEGER, SMALLINT, CHAR, VARCHAR,

NOT NULL

LNGVCHAR, DATE, TIME, TIMESTMP, GRAPHIC, VARGRAPH,

LONGVARG, FLOAT, DECIMAL, DBAINT, or DBAHW. These last two

data types are for information used only internally by the database

manager. (DBAINT data appears externally as INTEGER data; DBAHW

data appears externally as SMALLINT data.)

LENGTH

CHAR(7)

The size of the column as specified in the CREATE TABLE or ALTER

NOT NULL

TABLE statements. If the column has a data type of CHAR, VARCHAR,

GRAPHIC, or VARGRAPHIC, LENGTH contains the value specified on

the CREATE TABLE or ALTER TABLE statements.

If the data type is LONG VARCHAR, LENGTH contains 32767. If the

data type is LONG VARGRAPHIC, LENGTH contains 16383.

If the data type is DATE, TIME, or TIMESTAMP, LENGTH is blank.

For INTEGER and SMALLINT, LENGTH is blank. For FLOAT, LENGTH

contains the length supplied for FLOAT in the CREATE TABLE or ALTER

TABLE statements. If no value was supplied for a FLOAT column,

LENGTH is blank. If the data type is DECIMAL, the precision and scale

of the column are in this field, in the form: (pp,ss). For example: (11, 2).

Appendix C. DB2 Server for VSE & VM Catalog

381

SYSCOLUMNS

Column Name

Data Type

Description and Comments

SYSLENGTH

DBAHW

If COLTYPE is CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC, then

NOT NULL

this field contains the (maximum) length of the data, in bytes.

If COLTYPE is DATE, then SYSLENGTH contains 4.

If COLTYPE is TIME, then SYSLENGTH contains 3.

If COLTYPE is TIMESTAMP, then SYSLENGTH contains 10.

If COLTYPE is DECIMAL, then the first byte gives the number of digits

in the number, and the second gives the number of digits after the

decimal point. In other words, if COLTYPE is DECIMAL and LENGTH is

(pp,ss), then SYSLENGTH contains (256 x pp) + ss.

If COLTYPE is any one of the other numeric types, SYSLENGTH contains

the number of bytes occupied by a datum of that type: 2, 4, or 8.

SYSLENGTH never reflects the additional byte used internally to indicate

nulls or the halfword prefix for the length of VARCHAR or

VARGRAPHIC fields.

NULLS

CHAR(1)

The possible values are:

NOT NULL

if null values are allowed in this column.

if null values are not allowed in this column.

REMARKS

VARCHAR(254)

Information about the column supplied by a user by a COMMENT

NOT NULL

statement. The remarks are deleted from SYSCOLUMNS when the table

or view is dropped.

If the application server default CHARNAME setting supports mixed

data (that is, CCSIDMIXED is not 0), users can store mixed data (both

SBCS and DBCS characters) in the REMARKS column.

COLCOUNT

DBAINT

The number of unique values in the column. COLCOUNT initially

NOT NULL

contains -1 or 0. COLCOUNT captures only an approximate value for a

column which is not the first column of any index. See Note 1 for update

rules on this column.

HIGH2KEY

VARCHAR(12)

The first eight bytes of the second highest value in the column. (This

NOT NULL

value is needed internally.) This field initially contains blanks. If

FOR BIT DATA

COLTYPE is not CHAR, VARCHAR, LNGVCHAR, GRAPHIC,

VARGRAPH, or LONGVARG, then HIGH2KEY may be unprintable. See

Note 1 for update rules on this column.

LOW2KEY

VARCHAR(12)

The first eight bytes of the second lowest value in this column. (This

NOT NULL

value is needed internally.) This field initially contains blanks. If

FOR BIT DATA

COLTYPE is not CHAR, VARCHAR, LNGVCHAR, GRAPHIC,

VARGRAPH, or LONGVARG, then LOW2KEY may be unprintable. See

Note 1 for update rules on this column.

AVGCOLLEN

DBAHW

The average length of the values in this column. (This value is needed

NOT NULL

internally.) This value is initially -1. See Note 1 for update rules on this

column. The value is always -1 for long fields.

ORDERFIELD

CHAR(1)

The possible values are:

NOT NULL

if the rows are physically clustered in accord with the values in

this column.

when the table is created, then set to ’Y’ if further information

about this column exists. This value is reset to X’FF’ when the

index is dropped.

This entry is valid only for a column in a single-column index.

382

SQL Reference

SYSCOLUMNS

Column Name

Data Type

Description and Comments

CLABEL

VARCHAR(30)

A column label supplied by a user using a LABEL statement. The column

labels are deleted from SYSCOLUMNS when the table or view is

dropped.

If the application server default CHARNAME setting supports mixed

data (that is, CCSIDMIXED is not 0), users can store mixed data (both

SBCS and DBCS characters) in the CLABEL column.

COLINFO

CHAR(1)

This indicates the presence of additional information about the column

which will be found in the table SYSTEM.SYSCOLSTATS.

further information exists, see SYSTEM.SYSCOLSTATS.

NULL when the table is created, then set to ’Y’ if further information

about this column exists.

If this is the first column of an index and the index is dropped,

and there is no other index with this column as the first column,

this value is also set to NULL.

SUBTYPE

CHAR(1)

The subtype is applicable for CHAR, VARCHAR, and LONG VARCHAR

columns only. The possible values are: .

for bit data.

M for mixed data.

for SBCS data.

NULL if any of the following cases is true:

v The column is in a table migrated from SQL/DS Version 2

Release 2 or earlier and has a data type of CHAR, VARCHAR,

or LONG VARCHAR.

v The data type is not character.

CCSID

INTEGER

If CCSID conversion is required, it is done before data is stored in the

column. CCSID is applicable for CHAR, VARCHAR, LONG VARCHAR,

GRAPHIC, VARGRAPHIC, and LONG VARGRAPHIC columns only.

The possible values are:

1 to 65534

a valid CCSID for character or graphic data

65535

uniquely identifies bit character data

NULL

if any of the following conditions exist:

v the data type is character or graphic and the column

is in a table migrated from a release previous to

SQL/DS Version 3 Release 1

v the data type is neither character nor graphic.

FLDPROC

CHAR(1)

Indicates whether the column has a field procedure. The possible values

are:

NULL if a column belongs to a table migrated to SQL/DS Version 3

Release 1 or later

if the column has a field procedure

if the column does not have a field procedure.

Note 1:

The value is updated for all columns of the table by an UPDATE ALL

Appendix C. DB2 Server for VSE & VM Catalog

383

SYSCOLUMNS

STATISTICS statement on the table or a DBSPACE containing the table.

UPDATE STATISTICS has the same effect but only for the columns which

are the first columns of an index. CREATE or REORGANIZE INDEX

updates the value for one column, the first one in the index.

Note 2:

The SUBTYPE value is only used when the CCSID value is null. If a

SUBTYPE is encountered that is not valid and the CCSID value is null, a

SUBTYPE of SBCS is assumed.

Note 3:

More information on CCSIDs can be found in the DB2 Server for VM

System Administration or the DB2 Server for VSE System Administration

manual.

SYSDBSPACES

The SYSDBSPACES table contains a row for each PUBLIC and PRIVATE DBSPACE

in the database, including those DBSPACEs that no user has yet acquired. The

number of DBSPACEs available is determined during database generation. The size

of each DBSPACE is also specified at that time.

Additional DBSPACEs may be added from time to time by the ADD DBSPACE

operation.

The columns in SYSDBSPACES are:

Column Name

Data Type

Description and Comments

DBSPACENAME

VARCHAR(18)

The name given to the DBSPACE by the user who acquired it. If the

NOT NULL

DBSPACE has not been acquired, the field contains an empty string.

DBSPACENO

DBAHW

When the database is generated, the database manager assigns each

NOT NULL

DBSPACE a number (for internal use). DBSPACENO is that number. This

is the number you would use in the SHOW DBSPACE operator

command.

OWNER

CHAR(8)

The possible values are:

NOT NULL

blank

if the DBSPACE is not yet assigned.

owner

if the DBSPACE is PRIVATE.

PUBLIC

if the DBSPACE is PUBLIC.

¬¬¬¬¬¬¬¬

if the DBSPACE has been dropped, but has not yet been

removed from the database. In this case,

DBSPACENAME will contain the number of the

DBSPACE to be removed. Note that the hex value for

the symbols displayed is ’5F’X. Depending on the active

CHARNAME the symbol may not display as ’¬’ on

your terminal.

DBSPACETYPE

SMALLINT

The possible values are:

NOT NULL

if the DBSPACE is PUBLIC.

if the DBSPACE is PRIVATE.

NTABS

DBAHW

The number of tables contained in this DBSPACE. This field is updated

NOT NULL

when CREATE TABLE and DROP TABLE statements are issued.

384

SQL Reference

SYSDBSPACES

Column Name

Data Type

Description and Comments

NPAGES

INTEGER

The number of usable pages in the DBSPACE. NPAGES is specified in the

NOT NULL

PAGES parameter of the ACQUIRE DBSPACE statement.

CAUTION:

Changing NPAGES makes the dbspace appear to be a different size

without actually changing it. NPAGES should not be changed in a

production environment, to do so may cause errors to occur. It is

intended for testing purposes only.

NRHEADER

DBAHW

The number of pages to be used for the DBSPACE header. This number

NOT NULL

must be between 1 and 8. NRHEADER is specified in the NHEADER

parameter of the ACQUIRE DBSPACE statement.

PCTINDX

DBAHW

The percentage of pages to be used for indexes. PCTINDX is specified in

NOT NULL

the PCTINDEX parameter of the ACQUIRE DBSPACE statement.

FREEPCT

SMALLINT

The percentage of space on each page to be kept free when rows are

NOT NULL

inserted. Initially, the database manager gets the value for FREEPCT from

the PCTFREE parameter of the ACQUIRE DBSPACE statement. FREEPCT

can be updated by the PCTFREE parameter of the ALTER DBSPACE

statement.

For package DBSPACEs, FREEPCT is either 0 or 1. A FREEPCT of 0

indicates that the package DBSPACE is full. A FREEPCT of 1 indicates

that it is not full.

LOCKMODE

CHAR(1)

The possible values are:

NOT NULL

if the entire DBSPACE is to be locked.

if page locking is to be done in this DBSPACE.

if row locking is to be done in this DBSPACE.

LOCKMODE is updated by the LOCK parameter of the ACQUIRE

DBSPACE and ALTER DBSPACE statements.

NACTIVE

DBAINT

The number of active data pages in this DBSPACE (set to -1 when the

NOT NULL

DBSPACE is acquired). This value is the number of data pages that must

be read for a complete DBSPACE scan. The value includes all data pages

that contain stored rows for this DBSPACE. NACTIVE is set by an

UPDATE STATISTICS statement issued for this DBSPACE, or for any

table in this DBSPACE, or by creating or reorganizing any index in this

DBSPACE, or by the DATALOAD/RELOAD DBS Utility commands if the

statistics collection has not been turned off by SET UPDATE STATISTICS

OFF. NACTIVE is set to zero for an UPDATE STATISTICS statement

issued on a package DBSPACE.

POOL

DBAHW

The number of the storage pool into which the database manager places

NOT NULL

pages that belong to this DBSPACE. POOL is specified using the

STORPOOL parameter of the ACQUIRE DBSPACE statement.

If the value of POOL is negative, the storage pool is nonrecoverable. (The

absolute value of POOL is the storage pool number.)

SYSDROP

SYSDROP contains a list of tables and DBSPACEs waiting to be dropped. The

database manager uses this table when tables or DBSPACEs are dropped from the

database. When a DBSPACE or table is dropped, its description is dropped from

the catalog immediately, but the object is not dropped until the end of a logical

Appendix C. DB2 Server for VSE & VM Catalog

385

SYSDROP

unit of work (LUW). Instead, the database manager makes an entry in SYSDROP

identifying the dropped table or DBSPACE. When the LUW is committed

(implicitly or explicitly), all objects identified in SYSDROP are dropped. This

allows the LUW containing the DROP statement to proceed without waiting on

any locks held against the object being dropped, while guaranteeing that the object

will be dropped. It also minimizes the performance cost if the LUW must be rolled

back. Any LUW accessing the table or DBSPACE when the DROP statement is

issued will complete, but no further access to the object is possible. For information

on diagnosing problems associated with the DROP statement, see the DB2 Server

for VSE & VM Diagnosis Guide and Reference manual.

Column Name

Data Type

Description and Comments

DBSPACENO

DBAHW

The internal number of the DBSPACE containing an object to be dropped.

NOT NULL

TABID

DBAHW

The internal identifier of a table to be dropped. (In the DB2 Server for

NOT NULL

VSE & VM Diagnosis Guide and Reference manual, an internal table

identifier is known as a DBSS RID.)

QUALF

CHAR(1)

The possible values are:

NOT NULL

if the object to be dropped is a DBSPACE.

if the object to be dropped is a table.

SYSFIELDS

The SYSFIELDS table contains a row for each column that has a field procedure

associated with it. The columns in SYSFIELDS are:

Column Name

Data Type

Description and Comments

CREATOR

CHAR(8)

The owner of the package or view who created the table containing the

NOT NULL

column with the field procedure.

TNAME

VARCHAR(18)

The name of the table containing this column.

NOT NULL

COLNO

SMALLINT

The number of this column in the table.

NOT NULL

CNAME

VARCHAR(18)

The name of this column

NOT NULL

FLDTYPE

CHAR(8)

The data type of the encoded value in the field. Possible values are:

NOT NULL

INTEGER

for large integer.

SMALLINT

for small integer.

DECIMAL

for decimal.

FLOAT

for floating-point.

CHAR

for fixed length character string.

VARCHAR

for varying length character string.

GRAPHIC

for fixed length graphic string.

VARGRAPH for varying length graphic string.

386

SQL Reference

SYSFIELDS

Column Name

Data Type

Description and Comments

FLDLENGTH

SMALLINT

The length attribute of the field, or the precision for decimal fields. The

NOT NULL

number does not include the internal prefixes that may be used to record

the actual length and null state. The value in this column depends on the

data type of the field as follows:

For INTEGER

For SMALLINT 2

For DECIMAL

1 byte - precision of number 1 byte - scale of number

For FLOAT

For CHAR

Length of the string

For VARCHAR Maximum length of the string

For GRAPHIC Number of DBCS characters

For VARGRAPHIC

Maximum number of DBCS characters.

FPNAME

CHAR(8)

Name of the field procedure. Field procedure names are unique within

NOT NULL

an installation.

FPWORKAREA

SMALLINT

Size, in bytes, of the work area required for the encoding and decoding

NOT NULL

functions of the field procedure.

FPEXITPARML

SMALLINT

Length of the field procedure parameter value block.

NOT NULL

FPPARMLIST

VARCHAR(254)

The parameter list given after FIELDPROC in the statement that created

NOT NULL

the column. Insignificant blanks are removed.

SYSFPARMS

The SYSFPARMS table holds the field procedure value block contents for each field

procedure. Blocks longer than 254 characters will have more than one row in the

table. All field procedure value blocks of length greater than 0 will be recorded in

this table. The columns in SYSFPARMS are:

Column Name

Data Type

Description and Comments

FPNAME

CHAR(8)

Name of the field procedure. Field procedure names are unique within

NOT NULL

an installation.

CREATOR

CHAR(8)

The owner of the package or view who created the table that contains the

NOT NULL

column with the field procedure.

TNAME

VARCHAR(18)

Name of the table that contains the column with the field procedure.

NOT NULL

CNAME

VARCHAR(18)

Name of the column that has the field procedure.

NOT NULL

SEQNO

SMALLINT

Indicates the sequence of the portion of the parameter value block

NOT NULL

contained in this row. A long block may be divided among several rows

of the SYSFPARMS table. The value for the first portion of a block is 1.

Successive rows have sequential values.

FPEXITPARM

VARCHAR(254)

The parameter value block (or a portion of it) of the field procedure. This

NOT NULL

control block is passed to the field procedure when it is invoked.

Appendix C. DB2 Server for VSE & VM Catalog

387

SYSINDEXES

The SYSINDEXES table contains a row for every index currently in existence,

including the indexes that the database manager maintains on its own catalog

tables.

The columns in SYSINDEXES are:

Column Name

Data Type

Description and Comments

INAME

VARCHAR(18)

The name of the index.

NOT NULL

ICREATOR

CHAR(8)

The user ID of the person who created the index. The combination of

NOT NULL

INAME and ICREATOR uniquely identifies the index.

TNAME

VARCHAR(18)

The table on which the index is defined.

NOT NULL

CREATOR

CHAR(8)

The owner of the package or view who created the table on which the

NOT NULL

index is defined.

COLNAMES

VARCHAR(100)

This contains the first 100 characters of the names of the columns on

NOT NULL

which the index is defined. Each name is preceded by + (for ascending)

or - (for descending), and separated by commas and blanks. For example:

+AGE, +SALARY, -NEXM

INDEXTYPE

CHAR(1)

The possible values are:

NOT NULL

if the index is unique (duplicates not allowed).

if duplicates are allowed.

CLUSTER

CHAR(1)

The possible values are:

NOT NULL

if the index is clustered.

if the index is not clustered.

if the index was the first index created and is now clustered

(used for default insert clustering).

W if the index was the first index created and is now not clustered

(still the default insert index).

blank if this is an inactive primary key index.

The value of CLUSTER is not directly related to the CLUSTERRATIO

value.

See Note 1 for update rules on this column.

IID

DBAHW

The internal index identifier assigned to the index by DBSS.

NOT NULL

COLNUMBERS

VARCHAR(34)

An indicator array of binary integers of 15 bits (plus sign); it has one

NOT NULL

more element than the number of columns in the index. The first element

FOR BIT DATA

is the number of columns in the index. The second element is the column

number defining the major ordering of the index; the remaining elements

define the minor orders of the index. The column number is positive if

the index is ascending on that column, and negative if it is descending.

Each of these binary integer halfwords is stored in internal format.

The size of this field restricts index definitions to 16 columns.

388

SQL Reference

SYSINDEXES

Column Name

Data Type

Description and Comments

KEYLEN

DBAHW

This is used internally by the database manager. It is the average length

NOT NULL

of the key field. See Note 1 for update rules on this column.

FIRSTKEYCOUNT

DBAINT

This is used internally by the database manager. It gives the number of

NOT NULL

distinct values for the index, considering the first column only. See Note

1 for update rules on this column.

FULLKEYCOUNT

DBAINT

This is used internally by the database manager. It gives the number of

NOT NULL

distinct values for the index, considering all key columns. See Note 1 for

update rules on this column.

LOCKMODE

CHAR(1)

This is used internally by the database manager. It is:

NOT NULL

if key-interval locking is being performed on the index.

if the pages of the index are being locked.

LOCKMODE is updated using the LOCK parameter of the ACQUIRE

DBSPACE and ALTER DBSPACE statements. When you specify

LOCK=ROW on either the ACQUIRE DBSPACE or ALTER DBSPACE

statements, the database manager internally uses key-interval locking for

that DBSPACE. (Note that this applies only to PUBLIC DBSPACEs

because you cannot specify a different lock size for PRIVATE DBSPACEs.)

NLEAF

DBAINT

This is used internally by the database manager. It is the number of

NOT NULL

lowest-level pages in the index. See Note 1 for update rules on this

column.

NLEVELS

DBAHW

This is used internally by the database manager. It is the number of

NOT NULL

levels in the index tree. See Note 1 for update rules on this column.

IPCTFREE

SMALLINT

The amount of free space reserved in the index for later insertions and

NOT NULL

updates. IPCTFREE is specified in the CREATE INDEX statement and the

REORGANIZE INDEX command via the PCTFREE parameter.

CLUSTERRATIO

SMALLINT

This is used internally by the optimizer. The value here is a measure of

how clustered an index is. The value is a number between 0 and 10 000

where 10 000 represents a totally clustered index and 0 represents a

totally unclustered index. The value in this column is not directly related

to the CLUSTER value. See Note 1 for update rules on this column.

RELEASE

CHAR(5)

This column identifies the release for which the index was created. It

contains the value “2.1.0” for any release up to and including SQL/DS

Version 2 Release 1. For later releases, possible values are:

2.2.0 for SQL/DS Version 2 Release 2

3.1.0 for SQL/DS Version 3 Release 1

3.2.0 for SQL/DS Version 3 Release 2

3.3.0 for SQL/DS Version 3 Release 3

3.4.0 for SQL/DS Version 3 Release 4

3.5.0 for SQL/DS Version 3 Release 5

5.1.0 for DB2 Server for VSE & VM Version 5 Release 1

6.1.0 for DB2 Server for VSE & VM Version 7 Release 5

Non-unique indexes created under SQL/DS Version 2 Release 2 or later

have better performance characteristics than non-unique indexes from

previous releases.

Appendix C. DB2 Server for VSE & VM Catalog

389

SYSINDEXES

Column Name

Data Type

Description and Comments

KEYTYPE

CHAR(1)

This identifies whether the index is used for a primary key. The possible

values are:

if the index was created for a primary key which is active.

if the index was created for a key which is inactive.

if the index was created for a unique constraint which is active.

blank if the index was not created for a primary key or unique

constraint.

Note 1:

The value is updated for all indexes on a table or in the DBSPACE

by the UPDATE STATISTICS and UPDATE ALL STATISTICS

statements. CREATE and REORGANIZE INDEX updates the value

for the created or reorganized index.

SYSKEYCOLS

This table contains a row for every column in every key.

Column Name

Data Type

Description and Comments

TNAME

VARCHAR(18)

The name of the table on which the key is defined.

NOT NULL

TCREATOR

CHAR(8)

The owner of the package or view who created the table on which the

NOT NULL

key is defined.

KEYTYPE

CHAR(1)

The possible values are:

NOT NULL

primary key

foreign key

unique constraint

KEYNAME

CHAR(18)

This is the key name specified in the FOREIGN KEY clause (KEYTYPE =

NOT NULL

F) or the constraint name specified in the UNIQUE clause (KEYTYPE =

U). If the key or constraint name is not specified or it is a primary key,

the system generated name will be stored here.

CNAME

VARCHAR(18)

The column name.

NOT NULL

KEYORD

SMALLINT

The position of the column within the key.

NOT NULL

TABLEORD

SMALLINT

The position of the column within the table.

NOT NULL

DATACODE

SMALLINT

The data type of the column in internal form.

NOT NULL

SYSLENGTH

SMALLINT

This contains the length of the column. Its interpretation is the same as

NOT NULL

the SYSLENGTH column in the SYSCOLUMNS table.

TIMESTAMP

The date and time when this key was activated.

NOT NULL

390

SQL Reference

SYSKEYCOLS

Column Name

Data Type

Description and Comments

FLDPROC

CHAR(1)

Indicates whether this column has a field procedure associated with it.

Possible values are:

if yes.

if no.

NULL if the table containing the key was migrated from SQL/DS

Version 2 Release 2 or earlier.

CCSID¹

INTEGER

If CCSID conversion is required, it is done before data is stored in the

column. CCSID is applicable for CHAR, VARCHAR, LONG VARCHAR,

GRAPHIC, VARGRAPHIC, and LONG VARGRAPHIC columns only.

The possible values are:

1 to 65534

a valid CCSID for character or graphic data

65535

uniquely identifies bit character data

NULL

if any of the following conditions exist:

v the data type is character or graphic and the column

is in a table migrated from a release previous to

SQL/DS Version 3 Release 1

v the data type is neither character nor graphic.

Note 1:

More information on CCSIDs can be found in the DB2 Server for VM

System Administration or the DB2 Server for VSE System Administration

manual.

SYSKEYS

This table contains a row for each primary key, each foreign key, and each unique

constraint.

Column Name

Data Type

Description and Comments

TNAME

VARCHAR(18)

The name of the table on which the key or constraint is defined.

NOT NULL

TCREATOR

CHAR(8)

The owner of the package or view who created the table on which the

NOT NULL

key or constraint is defined.

KEYTYPE

CHAR(1)

The possible values are:

NOT NULL

primary key

foreign key

unique constraint

KEYNAME

CHAR(18)

This is the key name specified in the FOREIGN KEY clause (KEYTYPE =

NOT NULL

F) or the constraint name specified in the UNIQUE clause (KEYTYPE =

U). If the key name or the unique constraint name is not specified or it is

a primary key, the system generated name will be stored here. The

format of the generated name is ’PKEY’, ’FKEY’ or ’UKEY’ followed by a

special 12-bytes timestamp. The timestamp is the value of the

System/390 time of day clock when the key is defined and it is a string

of numbers and letters in base 35 representation. It is the same type of

timestamp that the database manager uses in the SYSTABAUTH table.

Appendix C. DB2 Server for VSE & VM Catalog

391

SYSKEYS

Column Name

Data Type

Description and Comments

KEYCOLS

SMALLINT

This is the number of columns that form the primary or foreign key, or

NOT NULL

unique constraint.

INAME

VARCHAR(18)

For a primary key this contains the name of the primary key index. For a

NOT NULL

foreign key, this field is blank. For a unique constraint this contains the

name of the index.

REFTNAME

VARCHAR(18)

For a foreign key, this field contains the name of the parent table. For a

NOT NULL

primary key and unique constraint, this field is blank.

REFTCREATOR

CHAR(8)

For a foreign key, this field contains the owner of the package or view

NOT NULL

who created the parent table. For a primary key and unique constraint

this field is blank.

DELETERULE

CHAR(1)

For a foreign key, this column gives the associated DELETE rule. The

NOT NULL

possible values are:

if the delete rule is RESTRICT.

if the delete rule is CASCADE.

if the delete rule is SET NULL.

For a primary key and unique constraint this field is blank.

STATUS

CHAR(1)

The current status of the key. The possible values are:

NOT NULL

if the key is active.

if the key is inactive.

if the foreign key is implicitly inactive. (Dependent on an

inactive primary key).

TIMESTAMP

The date and time when this key or constraint was activated.

NOT NULL

SYSLANGUAGE

The SYSLANGUAGE table contains the names of all currently installed national

languages (for example, English or French); that is, it is not a programming

language such as COBOL. A unique four-character code identifies each language

and a brief description, if necessary, is contained in the REMARKS column.

Unlike all other catalog tables, the owner of SYSLANGUAGE is SQLDBA. To view

SYSLANGUAGE use the statement:

SELECT * FROM SQLDBA.SYSLANGUAGE

The columns in SYSLANGUAGE are:

Column Name

Data Type

Description and Comments

LANGUAGE

VARCHAR(40)

The name of the national language.

NOT NULL

LANGKEY

CHAR(4)

The language key.

NOT NULL

REMARKS

VARCHAR(254)

Comments or description of the language.

LANGID

VARCHAR(5)

The VM-compatible language ID of the installed language.

392

SQL Reference

SYSOPTIONS

The SYSOPTIONS table describes the options and defaults that may be

implemented for this database. The table summarizes the information contained in

the text that follows the table.

Column Name

Data Type

Description and Comments

SQLOPTION

VARCHAR(18)

The name of the option being described by this row. SQLOPTION can be:

NOT NULL

RELEASE

if this row describes the release level of the database manager.

CHARNAME

if this row describes the name of the character set that is currently in effect.

DBCS

if this row describes the setting of the DBCS option.

CHARSUB

if this row describes the default subtype for character columns.

DATE

if this row describes the default format of DATE for the database manager.

TIME

if this row describes the default format of TIME for the database manager.

LDATELEN

if this row describes the length of the local (user defined) DATE format.

LTIMELEN

if this row describes the length of the local (user defined) TIME format.

DEFAULT LANGUAGE

if this row describes the default language used for ISQL HELP text.

CCSIDSBCS

if this row describes the default CCSID for SBCS character data and newly-created

SBCS character columns.

CCSIDMIXED

if this row describes the default CCSID for mixed character data and newly-created

mixed character columns.

CCSIDGRAPHIC if this row describes the default CCSID for graphic data and newly-created graphic

columns.

MCCSIDSBCS

if this row describes the default CCSID for migrated SBCS character columns.

MCCSIDMIXED

if this row describes the default CCSID for migrated mixed character columns.

MCCSIDGRAPHIC if this row describes the default CCSID for migrated graphic columns.

Appendix C. DB2 Server for VSE & VM Catalog

393

SYSOPTIONS

Column Name

Data Type

Description and Comments

VALUE

VARCHAR(18)

This describes the option.

NOT NULL

If SQLOPTION is RELEASE, then VALUE indicates the release level, such as “7.5.0” for DB2 Server for VSE

& VM Version 6 Release 1.

If SQLOPTION is CHARNAME, then VALUE indicates the value of CHARNAME that was specified when

the database manager was last started. The database management system is shipped with CHARNAME set to

INTERNATIONAL. (Character set information is stored in the SYSCHARSETS catalog table.)

If SQLOPTION is DBCS, then VALUE indicates whether the DBCS option is enabled. YES, in this case,

indicates that the DBCS option is enabled. NO indicates that it is not enabled. Note that a “YES” does not

necessarily mean that the DBCS option is currently in effect. It could be that the database administrator just

updated the value. If the value was just updated, then the DBCS option will not take effect until the database

manager is restarted.

If SYSOPTION is CHARSUB, then VALUE indicates the default character subtype to be used for the

database. The default is SBCS. MIXED is the other possible value. Note that the value indicated here does not

mean that value is currently in effect. It could be that the database administrator just updated the value. If the

value was just updated, then the value indicated will not be in effect until the database manager is restarted.

If SQLOPTION is DATE, then VALUE indicates the data format to be used for the database. The default for

DATE is ISO. However, JIS, USA, EUR or LOCAL may be used. Note that the value indicated here does not

necessarily mean that the value indicated for the option is currently in effect. It could be that the database

administrator just updated the value. If the value was just updated, then the value indicated will not be in

effect until the database manager is restarted.

If SQLOPTION is TIME, then VALUE indicates the TIME format to be used for the database. The default for

TIME is ISO. However, JIS, USA, EUR or LOCAL may be used. Note that the value indicated here does not

necessarily mean that the value indicated for the option is currently in effect. It could be that the database

administrator just updated the value. If the value was just updated, then the value indicated will not be in

effect until the database manager is restarted.

If SQLOPTION is LDATELEN, then VALUE indicates the length of the local DATE format. The default for

LDATELEN is 0, if no local DATE format is used. If a local DATE format is used, the length LDATELEN must

be greater than 9 and less than 255. Note that the value indicated here does not necessarily mean that the

value indicated for the option is currently in effect. It could be that the database administrator just updated

the value. If the value was just updated, then the value indicated will not be in effect until the database

manager is restarted.

If SQLOPTION is LTIMELEN, then VALUE indicates the length of the local TIME format. The default for

LTIMELEN is 0, if no local TIME format is used. If a local TIME format is used, the length LTIMELEN must

be greater than 7 and less than 255. Note that the value indicated here does not necessarily mean that the

value indicated for the option is currently in effect. It could be that the database administrator just updated

the value. If the value was just updated, then the value indicated will not be in effect until the database

manager is restarted.

If SQLOPTION is DEFAULT LANGUAGE, then VALUE indicates the default language to be used for ISQL

HELP text.

If SQLOPTION is CCSIDSBCS, then VALUE indicates the default CCSID for SBCS character data and

newly-created SBCS character columns.

If SQLOPTION is CCSIDMIXED, then VALUE indicates the default CCSID for mixed character data and

newly-created mixed character columns.

If SQLOPTION is CCSIDGRAPHIC, then VALUE indicates the default CCSID for graphic data and

newly-created graphic columns.

If SQLOPTION is MCCSIDSBCS, then VALUE indicates the default CCSID for migrated SBCS character

columns.

If SQLOPTION is MCCSIDMIXED, then VALUE indicates the default CCSID for migrated mixed character

columns.

If SQLOPTION is MCCSIDGRAPHIC, then VALUE indicates the default CCSID for migrated graphic

columns.

REMARKS

VARCHAR(254)

This contains remarks describing each row. The database manager places remarks in this column when the

NOT NULL

SYSOPTIONS table is created.

The following table shows the actual entries you would see in a newly-installed

SYSOPTIONS table.

394

SQL Reference

SYSOPTIONS

SQLOPTION

VALUE

REMARKS

RELEASE

7.5.0

VERSION, RELEASE, MODIFICATION

CHARNAME

INTERNATIONAL

CHARACTER SET FOR SQL STATEMENTS

DBCS

NO¹

WHETHER SO/SI CHARACTERS ARE RECOGNIZED

CHARSUB

SBCS

DEFAULT CHARACTER SUBTYPE COLUMNS. POSSIBLE VALUES:

SBCS,MIXED

DATE

ISO

DEFAULT DATE: ISO, JIS, USA, EUR, LOCAL

TIME

ISO

DEFAULT TIME: ISO, JIS, USA, EUR, LOCAL

LDATELEN

LOCAL DATE LENGTH: 0 OR 9 < LEN < 255

LTIMELEN

LOCAL TIME LENGTH: 0 OR 7 < LEN < 255

DEFAULT

S001

DEFAULT LANGUAGE FOR HELP TEXT

LANGUAGE

CCSIDSBCS

500

DEFAULT CCSID FOR SBCS DATA AND NEWLY CREATED SBCS

CHARACTER COLUMNS²

CCSIDMIXED

DEFAULT CCSID FOR MIXED DATA AND NEWLY CREATED MIXED

CHARACTER COLUMNS

CCSIDGRAPHIC

DEFAULT CCSID FOR GRAPHIC DATA AND NEWLY CREATED

GRAPHIC COLUMNS

MCCSIDSBCS

DEFAULT CCSID FOR MIGRATED SBCS CHARACTER COLUMNS

MCCSIDMIXED

DEFAULT CCSID FOR MIGRATED MIXED CHARACTER COLUMNS

MCCSIDGRAPHIC

DEFAULT CCSID FOR MIGRATED GRAPHIC COLUMNS

Note

The CHARNAME value must be a mixed CHARNAME and DBCS value

must be set to YES for DBCS character support.

Note

More information on CCSIDs can be found in the DB2 Server for VM

System Administration or the DB2 Server for VSE System Administration

manual.

Note

Though 37 is the default CCSID migration value, 500 is the installation

default.

SYSPARMS

The SYSPARMS table describes the parameters for the stored procedures defined. It

contains a row for each parameter of each stored procedure. Table Table 24 shows

the definition of this catalog table.

Table 24. Definition of SYSTEM.SYSPARMS

Column Name

Data Type

Description

NAME

CHAR(18) NOT

The name of the STORED procedure with which this parameter is

NULL

associated.

AUTHID

CHAR(8) NOT NULL

The authorization ID associated with this version of the stored

procedure. See Table 28 on page 400 for an example of using the

AUTHID column.

Appendix C. DB2 Server for VSE & VM Catalog

395

SYSPARMS

Table 24. Definition of SYSTEM.SYSPARMS (continued)

Column Name

Data Type

Description

PARMNAME

CHAR(18) NOT

The name of the parameter, or blank. This column is included for

NULL

compatibility with other database products. It is ignored by DB2

Server for VSE & VM.

ROUTINEID

INTEGER NOT NULL

Internal identifier of the stored procedure.

ROWTYPE

CHAR(1) NOT NULL

The type of the parameter. Possible values are:

Input parameter

Output parameter

Both input and output

ORDINAL

SMALLINT NOT

The ordinal number of the parameter within the parameter list.

NULL

TYPENAME

CHAR(18) NOT

The name of the data type of the parameter.

NULL

DATATYPEID

SMALLINT NOT

The internal ID of the data type of the parameter.

NULL

LENGTH

INTEGER NOT NULL

Maximum length of the data type or the precision of the parameter.

SCALE

SMALLINT NOT

Scale of the parameter, if the data type is decimal. 0 otherwise.

NULL

SUBTYPE

CHAR(1) NOT NULL

If the data type of the parameter is character, this column contains

the character subtype. Possible values are:

The subtype is FOR BIT DATA.

The subtype is FOR SBCS DATA.

M The subtype is FOR MIXED DATA.

blank The data type of the parameter is not character.

CCSID

INTEGER NOT NULL

For all character and graphic data types, this column contains the

CCSID that the stored procedure assumes this parameter will be

tagged with. 0 for datatypes other than character and graphic.

SYSPROGAUTH

SYSPROGAUTH records privileges of users to run packages, and to grant these

privileges to other users. For the DB2 Server for VSE & VM database manager, a

program is a package stored in the database. The columns in SYSPROGAUTH are:

Column Name

Data Type

Description and Comments

GRANTOR

CHAR(8)

The user ID of the person who

NOT NULL

granted the RUN privilege.

GRANTEE

CHAR(8)

The user ID of the person who holds

NOT NULL

the RUN privilege. If the userid is

PUBLIC, the program may be run by

all users.

CREATOR

CHAR(8)

The owner who preprocessed the

NOT NULL

program. CREATOR.PROGNAME

uniquely identifies the package that

may be run by the grantee.

396

SQL Reference

SYSPROGAUTH

Column Name

Data Type

Description and Comments

PROGNAME

VARCHAR(8)

The name of the package that may be

NOT NULL

run by the grantee. The name is

obtained from the PREPNAME

preprocessor parameter.

CREATOR.PROGNAME is the

complete name of the package.

TIMESTAMP

CHAR(12)

The value of the System/390 time of

NOT NULL

day clock when the grant was made.

This value is used internally when

privileges are revoked; it is stored as

a string of numbers and letters.

RUNAUTH

CHAR(1)

The possible values are:

NOT NULL

if the user is allowed only to

run the package.

if the user may also grant

the RUN privilege on the

package to someone else.

SYSPSERVERS

SYSTEM.SYSPSERVERS is added to allow the database administrator to define the

stored procedure servers at which stored procedure run, and to put them in

groups. This allows the database administrator to tune the stored procedure

workload. This table is unique to DB2 Server for VSE & VM. Table Table 25 shows

the definition of this catalog table.

Table 25. Definition of SYSTEM.SYSPSERVERS

Column Name

Data Type

Description

PSERVER

CHAR(8) NOT NULL

The name of the stored procedure server. This name must not

contain any embedded blanks. If it does, any attempt to start the

stored procedure server fails. Note that a PSERVER can be in only

one group.

See “SYSPSERVERS” for more information on the PSERVER

column.

SERVGROUP

CHAR(18)

The group that this server is in. Grouping the stored procedures

enables the database administrator to tune the stored procedure

workload. For example, if the database manager wanted to dedicate

servers ACT1, ACT2, and ACT3 to accounting-related stored

procedures, he could define them all in the same group, perhaps

called ACCOUNT. In SYSTEM.SYSROUTINES, the row for any

accounting-related stored procedure would specify ACCOUNT in

the SERVGROUP column. When one of these stored procedures is

invoked, the database manager will select a free server from the

ACCOUNT group to run the stored procedure.

Any server for which the SERVGROUP column is NULL is in the

default server group.

AUTOSTART

CHAR(1)

Indicates whether the stored procedure server should be started

when the database manager is started. A value of ’Y’ indicates that

the server should be autostarted. ’N’ or NULL indicates that it

should not be autostarted. The default value for this column is

NULL.

Appendix C. DB2 Server for VSE & VM Catalog

397

SYSPSERVERS

Table 25. Definition of SYSTEM.SYSPSERVERS (continued)

Column Name

Data Type

Description

DESCRIPTION

CHAR(254)

An optional column, in which the database manager can provide

information such as the stored procedures that use this server

group, specifications (for example virtual storage requirements) for

servers in this group, and so on. The default value for this column

is NULL.

Table 26 shows an example of a SYSTEM.SYSPSERVERS table.

Table 26. Sample SYSTEM.SYSPSERVERS Table

PSERVER

SERVGROUP

AUTOSTART

DESCRIPTION

PROCSRV1

Default server group

PROCSRV2

BILLING

PROCSRV3

BILLING

PROCSRV4

DAY_RPT

In Table 26, the first row identifies the only server in the default server group. The

second and third rows identify the servers that are in the group BILLING. The

fourth row identifies the only server in the group DAY_RPT.

SYSROUTINES

SYSROUTINES allows the database administrator to specify the load module or

phase name and package name for a given stored procedure, and to specify the

stored procedure server at which it will run. Note that several of the columns in

SYSTEM.SYSROUTINES in DB2 Server for VSE & VM correspond to columns in

stored procedure related catalog tables in DB2 for MVS and have been given the

same name. However, both tables have system-unique columns, and as a result the

definitions of the tables are not identical. Table 27 shows the definition of

SYSTEM.SYSROUTINES.

Table 27. Definition of SYSTEM.SYSROUTINES

Column Name

Data Type

Description

NAME

CHAR(18) NOT

The name of the STORED procedure. This is the name that is

NULL

specified in the SQL CALL statement.

AUTHID

CHAR(8) NOT NULL

The authorization ID that will be running this stored procedure.

The AUTHID column can be used to qualify which

SYSTEM.SYSROUTINES row is used to determine the load module,

run time options, and so on, to use when a particular stored

procedure is invoked. Possible reasons to use the AUTHID column

include:

v To restrict the use of a stored procedure to a particular

authorization ID

v To enable a particular authorization ID to test a new version of a

stored procedure

v To allow different authorization IDs to use different versions of a

stored procedure

If AUTHID for a stored procedure is blank, any authorization ID

can run that stored procedure. See Table 28 on page 400 for an

example of using the AUTHID column.

398

SQL Reference

DB2 Server for VSE & VM SQL Reference (Version 7 Release 5) - page 10