&NDBINFO

Retrieves information about an NDB database.

&NDBINFO dbname { DB | [ FIELD ] { NAME=fieldname | NUMBER=n } }
                [ FORMAT=formatname [ FSCOPE={ PROCESS | GLOBAL } ] ]
                [ FULL | SHORT ]

The &NDBINFO verb allows an NCL procedure to obtain information about an NDB, including information about the database itself, and information about the fields defined in it.

The information is returned in NCL variables.

Operands:

dbname

Specifies the name of the NDB that you wish to retrieve information about, and is a mandatory operand. The NDB must have been previously opened by an &NDBOPEN statement.

DB

Indicates that information about the database itself is to be returned. The information is returned in the following NCL variables:

NDBDBNAME contains the database name, as coded on the &NDBINFO statement
NDBDBVKL contains the VSAM key length of the database
NDBDBVRL contains the VSAM maximum data length of the database
NDBDBNFLDS contains the number of currently defined fields in the database
NDBDBNRECS contains the number of records in the database
NDBDBNRID contains the next RID the database will use on an &NDBADD statement
NDBDBLANG contains the language code that describes the database's uppercase table If null, the standard (US-format) uppercase table is used
NDBDBLOADMD indicates if the database is in load mode or not Values are NO and YES
NDBDBRIDRU indicates the RID reuse status for the database Values are:
- N/S—not supported
- POSS—possible (supported but not enabled)
- ENAB—enabled but not presently active
- ACT—presently active
- ACT/C—active with a KEYSTATS scan (collection) in progress
- COLL—a KEYSTATS scan is in progress, but reuse is not currently active (no ranges available)
NDBDBRIDNRU contains the number of reused RIDs handed out since the last KEYSTATS run
NDBDBRIDNNW contains the number of new RIDs handed out since the last KEYSTATS run
NDBDBIVERS contains the internal version of the database as 4 digits. RID reuse became available at 0510

[ FIELD ] NAME=fieldname

Indicates that information about the named field is to be returned. If the named field does not exist in the nominated NDB, then &NDBRC is set to 3. The information returned is listed below.

[ FIELD ] NUMBER=n

Specifies that information about relative field number n is to be returned. This number must be from 1 to the value returned by an &NDBINFO DB request in &NDBDBNFLDS. The information returned on the field requests is set into NCL variables as shown:

&NDBFLDNAME contains the name of the field
&NDBFLDFMT contains the field format, values CHAR, NUM, HEX, FLOAT, or DATE
&NDBFLDKEY contains the field key option, values NO, YES, UNIQUE, or SEQUENCE
&NDBFLDNULLF contains the NULLFIELD option, values YES or NO
&NDBFLDNULLV contains the NULLVALUE option, values YES or NO
&NDBFLDUPD contains the UPDATE option, values YES or NO
&NDBFLDCAPS contains the CAPS option, values YES, NO, or SEARCH
&NDBFLDDESC contains the field description, if one was defined; otherwise it contains one blank
&NDBFLDUSER1 contains the USER1 information if present; otherwise, it contains one blank
&NDBFLDUSER2 contains the USER2 information if present; otherwise, it contains one blank
&NDBFLDUSER3 contains the USER3 information if present; otherwise, it contains one blank
&NDBFLDUSER4 contains the USER4 information if present; otherwise, it contains one blank
&NDBFLDMAXL contains the maximum length, in characters, that the field can hold. For CHAR fields, this is 255 if not keyed, and (VSAM keylen - 8) if keyed. For NUM fields, is 15. For HEX fields, this is 254 if not keyed, and ((VSAM keylen - 9) * 2) if keyed. For DATE fields, this is 8.
&NDBFLDINTID contains a 4-digit hexadecimal string (representing a 2-byte value) that is the internal field identification inside the NDB
&NDBFLDBASE contains the value DEC or HEX for numeric format fields and the value NONE for all other formats
&NDBFLDKSTAT contains a string of blank-separated numbers as follows:
dddddddd ff a b c d e f g h i j k l m n o p q r s

where:
- dddddddd is the date that key statistics were last collected for this field, in the format YYYYMMDD. If this number is all zeros, no key statistics have been collected for it.
  Note: Even non-keyed fields will have a non-zero date set after key statistics collection, but all other fields will be zero.
- ff is two flag characters, each equal to Y (meaning YES) or N (meaning NO). The first is Y if a table overflow occurred while calculating the modal unique value occurrence (see the description of fields g and h following). The second flag is Y if a table overflow occurred while calculating the modal VSAM record occurrence (see the description of fields l and m following).
- a is the number of unique values found for this key.
- b is the total number of VSAM records that hold information about records having this key.
- c is the number of times this key field actually exists in data records.
- d, e, and f are the minimum (d), maximum (e), and average (f) number of records that have the same unique value. For example, for a unique key, d, e, and f are all 1, or zero if no records have the field present.
- g is the modal number of records that have the same unique value; that is, the most often occurring count of same-numbers of each unique value.
- h is the count for the modal value. For example, if the most often occurring count of a unique value is 35, then g is 35. If this count occurs 97 times, then h is 97. If the first of the two flags (ff) described is set to Y, this modal count (h) might not be correct, as the table used to keep counts/occurrences can overflow.
- i, j, and k are the minimum (i), maximum (j), and average (k) numbers of VSAM records used to hold information for any unique value.
- l is the modal number of VSAM records for any unique value; that is, the most often occurring count of same-numbers of VSAM records for each unique value.
- m is the count for the modal value. For example, if the most often occurring count of VSAM records for a unique value is 27, then l is 27. If this count occurs 115 times, then m is 115. If the second of the two flags (ff) described is set to Y, this modal count (m) might not be correct, as the table used to keep counts/occurrences can overflow.
- n, o, p, q, r, and s are the minimum (n), maximum (o), average (p), modal (q), modal count (r), and all-equal (s) key lengths for this field. The key length statistics represent significant key field characters (NDBs use VSAM KSDSs, and the VSAM key length is fixed for each individual NDB). For a character field, this is the length less trailing blanks. For a HEX field, this is the exact length provided (trailing X'00's are significant).
  Numeric, date, and floating point fields are all fixed length-4, 3, and 8 respectively. In this case, all the values except modal count are that value (modal count equals the number of VSAM records. The modal value calculation cannot overflow for key length statistics.
  
  The all-equal key length is the maximum key length, in all cases (including fixed length fields), where all values found had the same prefix. It can range from zero to the maximum key length. (It is used by the scan optimizer in weighing generic and range requests.)
  
  The maximum number of records having any unique value is greater than one for a field defined as KEY=UNIQUE. This is because NDB ALTER OPT=BLDX can tolerate unique key violations during index build, allowing you to correct them later.

FORMAT=formatname [ FSCOPE={ PROCESS | GLOBAL } ]

FORMAT=formatname indicates that the format formatname, defined on the &NDBFMT statement, is to be used. The nominated format must exist in the nominated scope. PROCESS is the default, and indicates a format defined by the current NCL process. GLOBAL indicates that a format is to be found in the global format pool for the NDB.

If the format does not exist, a response code of 20 is returned. If the format exists, a set of variables is returned, as follows:

&NDBFMTUSAGE

Contains the value INPUT if this is an input format (that is, usable on an &NDBGET statement), or the value OUTPUT if this is an output format (for use with &NDBADD or &NDBUPD verbs).

&NDBFMT0

Contains the number of &NDBFMTn NCL variables returned.

&NDBFMT1 to &NDBFMTn

Contain strings of blank-separated values that describe either an individual field entry or a link to another record.

If the format is for input use, and the variable describes a return field, it is in the following format:

a b c d e f g h i

where:

a is the return NCL variable name.
b is the return format. If no editing was applied (justify, pad, and so on), this is the same as the database variable format (d). If editing was done, it is CHAR.
c is the name of the NDB field that the data is coming from.
d is the format of the NDB field: CHAR, NUM, HEX, DATE or FLOAT.
e is the format length requested. It is zero if not specified.
f is the format pad character. If not specified, or blank, or if the format length (e) is zero, the return value is a single dash character (-). Otherwise, it is the pad character quoted (for example, a per cent sign). If the pad character is a single quote, it is quoted using double quote characters.
g is the justify option—LEFT, CENTER, or RIGHT. If the format length (e) is zero, g is returned as a single dash (-).
h is the NULLFIELD return option—DELETE, NULLVAL, PAD, or NORETURN.
i is a single dash (-) at present (reserved for a possible date format option).

If the format is for input use, and the variable describes a link to another record, it is in the following format:

a b c d e f

where:

a is the literal .LINK, which is an invalid variable name, thus distinguishing this information from a variable description.
b is the ID value assigned to this link, or a single dash (-) if none was specified.
c is the from-ID value for this link, or a single dash if none was specified-meaning the from record is the primary retrieval record.
d is the from NDB field name.
e is the to NDB field name—it must be keyed.
f is the no find option—it is the word IGNORE or a number from 10 to 19.

If the format is for output use, it is in the following format:

a b c d

where:

a is the source NCL variable name from which data will be extracted.
b is the target NDB field name that will be set.
c is the target NDB field format (CHAR, and so on).
d is additional information—currently, *TEMP is always a dash.

The order of returned information in these variables agrees with the original format definition in that field for a specific record or link definition. However, the order of field description entries is not necessarily the same as the original format, as generic or range field specifications are expanded out and all fields are returned in internal ID order within a record or link.

FULL | SHORT

The FULL option requests that all information about the field be returned. The SHORT option requests that the only information that is obtained without reading the NDB be returned. The following fields are not returned:

&NDBFLDDESC
&NDBFLDUSER1, 2, 3, and 4
&NDBFLDKSTAT

Examples: &NDBINFO

The following example returns information about the NDB called MYNDB in NCL variables &NDBDB... (as described previously).

&NDBINFO MYNDB DB

The next example returns information about the field called 'SURNAME' in MYNDB. The returned information is in NCL variables &NDBFLD... (as described previously).

&NDBINFO MYNDB NAME=SURNAME

The following example returns information about all the fields defined in MYNDB.

&NDBINFO MYNDB DB
&I = 1
&DOWHILE &I LE &NDBDBNFLDS
   &NDBINFO MYNDB NUMBER=&I
   ... process field definition
   &I = &I + 1
&DOEND

Notes:

Errors encountered whilst processing the &NDBINFO statement may cause the procedure to terminate, or may just be reflected in the &NDBRC system variable, depending on the setting of &NDBCTL ERROR option.

The nominated NCL variables are not updated if a nonzero response is returned in &NDBRC (for example, named field not found).

The &NDBINFO statement makes it easy to write generalized NCL procedures to manipulate NDBs. The procedures can open any NDB and, by using &NDBINFO statements, build tables of control information for further processing.

In a similar way, a generalized database unload/reload utility is constructed.

Note: See also the NDB CREATE command description in the Online Help.

More information:

&NDBDEF