|
SELCOPY
Examples Menu
SYSIBM.SYSCOLUMNS is the DB2 table which describes every column of every
table within a DB2 SubSystem. For our example we'll list from it a few
columns of interest for a specified table and use the CHAR option to return
all information in displayable character format.
read F1 tab=SYSIBM.SYSCOLUMNS char \
fmt='name,TBNAME,TBCREATOR,COLNO,COLTYPE,LENGTH,SCALE' \
where="TBNAME='TYPE_TEST'" \
order='colno'
print * Print the row.
The code for this SQL operation is quite long, so we have used the SELCOPY
statement continuation feature. The continuation character is a backslash
(\) as the last character of a control statement.
The following is output from the above control cards with a subroutine
added which generates column headings by analysing the SQL Descriptor Area
using the SELCOPY positional keyword SQLDA.
SELCOPY REL 9.80 AT CBL-VMPS31 MVS 5.2.2 OS JOB=P390A 13.47 WED 14 MAY 1997 PAGE 1
o ---------------------------------------- -------------------------------------- -------- o
o ** CBL.SSC.CTL(DB2SBR2) *** L=101 +++ 97/05/14 13:47:19 (P390A) o
* Demonstrate Table Column listing.
o o
equ hd 1001
opt w 2222
o o
rd f1 tab=sysibm.syscolumns char \
fmt='name,TBNAME,TBCREATOR,COLNO,COLTYPE,LENGTH,SCALE' \
o where="TBNAME='TYPE_TEST'" \ o
1. order='colno'
o o
2. do heading stopaft=1
o 3. print * Print the row. o
4. goto get
o o
==heading==
o ------- o
5. @n = 2 at sqlda+14 type=b * Number of cols in use.
6. @sqlv = sqlda+16 * Point to 1st var.
o 7. pos uxatptr = 4 at @sqlv+4 * Sets @ -> 1st col in workarea. o
8. @1stc = @ * Keep as @1stc.
o =loop= o
----
9. pos uxatptr = 4 at @sqlv+4 * Sets @ -> this col in workarea.
o 10. pos hd+@-@1stc-1 = '|' o
11. pos hd+@-@1stc-1+100 = '|'
12. pos hd+@-@1stc = 18 at @sqlv+14
o o
if @n > 1
o 13. then @n=@n-1 o
14. then @sqlv=@sqlv+44
15. then goto loop
o o
16. pr fr hd
o 17. tran Lrecl at hd+100 ' ' '-' o
18. pr fr hd+100
19. =ret=
o o
INPUT SEL SEL 1 RECORD
o RECNO TOT ID. 1 2 3 4 5 6 7 8 9 0 LENGTH o
----- --- --- ....,....0....,....0....,....0....,....0....,....0....,....0....,....0....,....0....,....0....,....0 ------
1 1 16 NAME |TBNAME |TBCREATO|COLNO |COLTYPE |LENGTH|SCALE 76
o 1 1 18 ------------------|------------------|--------|------|--------|------|------ 76 o
1 1 3 TTINT |TYPE_TEST |P390 | 1|INTEGER | 4| 0 76
2 2 3 TTSINT |TYPE_TEST |P390 | 2|SMALLINT| 2| 0 76
o 3 3 3 TTDEC |TYPE_TEST |P390 | 3|DECIMAL | 7| 2 76 o
4 4 3 TTCHAR |TYPE_TEST |P390 | 4|CHAR | 8| 0 76
5 5 3 TTVCHAR |TYPE_TEST |P390 | 5|VARCHAR | 30| 0 76
o 6 6 3 TTFLOAT |TYPE_TEST |P390 | 6|FLOAT | 8| 0 76 o
7 7 3 TTREAL |TYPE_TEST |P390 | 7|FLOAT | 4| 0 76
....,....0....,....0....,....0....,....0....,....0....,....0....,....0....,....0....,....0....,....0
o o
SUMMARY..
SEL-ID SELTOT FILE BLKSIZE LRECL FSIZE CI DSN
o ------ ------ ---- ------- ----- ----- -- --- o
1 7 READ F1 76 U 7
2 1
o 3----4 7 o
5----8 1
9---12 7
o 13---15 6 o
16---19 1
o o
** ** ** ** ** ** ** SELCOPY IS LICENSED BY COMPUTE (BRIDGEND) LTD +44 (1656) 652222 & 656466 ** ** ** ** ** ** **
** EXPIRY DATE -- 12 JUN 2001 **
|
The following special arguments for the SELCOPY POS keyword are supported:
- POS SQLCA
- Refers to the SQL Communication Area.
- POS SQLDA
- Refers to the SQL Descriptor Area.
- POS SQLMA
- Refers to the SQL Message Area.
|
