|
|
|
|
|
The output of the SQL statement is formatted as a report with the various fields listed in order of their appearance in the SQL SELECT statement and according to their field types (see Table 259). Therefore, the total width of the output is the sum of the widths of the individual fields.
The width of each field is the maximum width of the field as defined in the DB2 table. The fields are contiguous.
The following chart lists commonly used DB2 field types (including how field lengths are calculated) and the Control-M/Analyzer built-in process used by the DO EXTRACT statement to process the value of the extracted DB2 field. For information about other DB2 field types, refer to your DB2 documentation.
Table 259 Commonly-Used DB2 Field Types and Control-M/Analyzer Built-In Process Used by DO EXTRACT to Extract their Values
Field Type |
Description/Structure |
Length |
Built-in Process |
|---|---|---|---|
CHARACTER(n) |
String |
n |
None required |
VARCHAR(n) |
Variable length character string. The first 2 bytes contain the actual string length |
n+2 |
Start extraction from third byte |
SMALLINT |
Halfword binary integer |
2 |
BBINH |
INTEGER |
Fullword binary integer |
4 |
BBINF |
DECIMAL(p,s) NUMERIC(p,s) |
Decimal value. Precision (p) is the total number of digits. Scale (s) is the number of digits after the implied decimal point. |
INT (p/2+0.5) |
BUNPKs |
DATE |
System datea |
10 |
None required |
TIME |
Time, according to a 24-hour clock |
8 |
None required |
TIMESTAMP |
Date and time (as previously defined by DATE and TIME type fields)a |
26 (including |
None required |
All field types that may contain a NULL value (NULL=YES as defined in DB2) include an additional X ‘00’ at the end of the field. Therefore, the field length for these field types should be increased by one. The maximum length of a value that Control-M/Analyzer can extract is 100 bytes. When extracting fields from DB2 files, BMC recommends that each field be extracted individually. Do not extract an entire line from a DB2 file into one field. If a field in the DB2 table is empty (NULL), the field is padded with X ‘00’ when extracted by Control-M/Analyzer.
To receive information on DB2 table column lengths and types, the database administrator can issue the following DB2 command:
SELECT * FROM SYSIBM.SYSCOLUMNS WHERE TBCREATOR='creator' AND TBNAME='name';
where
Parent Topic |