|
Querying Column Names in
DB2 Using the DESC REXX EXEC
By Kannan AK
In DB2, there is no direct way of querying the column names of
a table like there is with the Unix-style/SQL *PLUS Describe command.
The SQL *PLUS Describe command lists the column definitions for
the specified table, view or synonym. An example is given below
from SQL *PLUS.
To describe the table EMP, the user enters:
SQL> DESCRIBE
EMP
SQL*Plus lists the
following information: |
| Name |
Null? |
Type |
EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO |
NOT NULL |
NUMBER(4)
CHAR(10)
JOB(9)
NUMBER(4)
DATE
NUMBER(7,2)
NUMBER(7,2)
NUMBER(2)
|
In mainframe DB2, a programmer needs to quickly consult documentation
to seek this same information. Or, if they are available, they
can use other utilities like File-Aid DB2 from Compuware. To
get the column names for the SQL they are writing, programmers
need a quick and easy command. Best of all is if the command
output can be referred to (or copy and pasted) from one ISPF
split screen session into another. For example, the programmer
might have SPUFI active on one logical session while they run
the DESC REXX EXEC on another.
In this simple online invocation of DSNTEP2, only a few significant
columns are retrieved but the DESC EXEC can be modified to suit
any need with great ease. Refer to the SQL embedded in the DESC
EXEC itself and change the columns that you want.
Please substitute the correct parameter for the file name in your
system for Library statement in RUN PROGRAM (DSNTEP2). The EXEC
expects the DB2 subsystem and the tablespace.tablename as run-time
parameters at invocation. These parameters are queried in the IBM-reserved
table named SYSIBM.SYSCOLUMS to return the description of the object.
To get the field names as defined in the table, a fieldname COLNO
is added with the ORDER BY clause. The parameter LHS means Left
Hand Side alignment.
Refer to Figure 1 for an example of how to execute the DESC EXEC.
Figure 2 shows a sample of what to expect as output.
Refer also to the screen
example that shows
an actual screen image of the DESC output.
Figure 1 – Once the DESC EXEC has been placed into
the SYSEXEC or SYSPROC concatenation of a TSO session, it
can be executed from anywhere within ISPF by using the "TSO" ISPF
command. The first parameter is the DB2 subsystem name. The
second is the tablespace.tablename being queried. Omitting
parameters will return the required syntax.
Command Invocation:
TSO DESC DSP2 SYSIBM.SYSDBRM
TSO DESC (Returns the syntax)
|
Figure 2 – The output of the DESC REXX EXEC.
Output:
For illustration purpose SYSIBM.SYSDBRM has been used.
***INPUT,STATEMENT:,
SELECT NAME, COLTYPE, LENGTH, SCALE, NULLS
FROM SYSIBM.SYSCOLUMNS
WHERE TBNAME = 'SYSDBRM'
AND TBCREATOR = 'SYSIBM'
ORDER BY COLNO
WITH UR;
+---------------------------------------------------------------+
| NAME | COLTYPE | LENGTH | SCALE | NULLS |
+---------------------------------------------------------------+
1_| NAME | CHAR | 8 | 0 | N |
2_| TIMESTAMP | CHAR | 8 | 0 | N |
3_| PDSNAME | CHAR | 44 | 0 | N |
4_| PLNAME | CHAR | 8 | 0 | N |
5_| PLCREATOR | CHAR | 8 | 0 | N |
6_| PRECOMPTIME | CHAR | 8 | 0 | N |
7_| PRECOMPDATE | CHAR | 6 | 0 | N |
8_| QUOTE | CHAR | 1 | 0 | N |
9_| COMMA | CHAR | 1 | 0 | N |
10_| HOSTLANG | CHAR | 1 | 0 | N |
11_| IBMREQD | CHAR | 1 | 0 | N |
12_| CHARSET | CHAR | 1 | 0 | N |
13_| MIXED | CHAR | 1 | 0 | N |
14_| DEC31 | CHAR | 1 | 0 | N |
15_| VERSION | VARCHAR | 64 | 0 | N |
16_| PRECOMPTS | TIMESTMP | 10 | 0 | N |
+---------------------------------------------------------------+
SUCCESSFUL RETRIEVAL OF 16 ROW(S)
|
DESC REXX Source Code
|
