View columns for SQL tables, etc. located in QTEMP

One of the problems of using the SQL Views to list the columns of SQL Tables, Views, etc. is that they do not include objects in the library QTEMP. The latest round of Technology Refreshes, IBM i 7.4 TR3 and 7.3 TR9, has brought us a View that allows me to get to the information of those SQL objects in QTEMP.

Most people use the SQL View SYSCOLUMNS to learn about the columns in their SQL objects. For several IBM i releases has had a similar View SYSCOLUMNS2 that contains all the same columns, even using the same column names, and with ten additional columns not found in SYSCOLUMNS. If you compare the list of columns in both Views the last column common to both is HAS_FLDPROC.

IBM recommends us to use SYSCOLUMNS2 rather than the other view as:

... a query that uses SYSCOLUMNS2 will typically perform better than querying SYSCOLUMNS.

As I mentioned earlier neither SYSCOLUMNS nor SYSCOLUMNS2 contain data for objects in QTEMP. The new View SYSCOLUMNS_SESSION covers that shortfall. It lists the details SQL objects in QTEMP only. The columns in the new View are identical to those returned by SYSCOLUMNS2.

Let me start by building the two objects I will be using in my examples. All of these statements are being run in ACS's "Run SQL Scripts":

01 CL:DSPOBJD OBJ(MYLIB/*ALL) OBJTYPE(*ALL) OUTPUT(*OUTFILE) OUTFILE(QTEMP/WDSPOBJD) ; 02 CREATE TABLE QTEMP.OBJECT_LIST AS (SELECT OBJNAME,OBJTYPE,OBJATTRIBUTE FROM TABLE(QSYS2.OBJECT_STATISTICS('MYLIB','ALL'))) WITH DATA ;

Both of these statements do the same thing, give me a list of the objects in my library.

Line 1: I have shown before how I can execute a CL command in ACS. To be honest I do not remember all of the parameters for the DSPOBJD command. Fortunately I can prompt the command using F4, and a new window is opened that allows me to enter the parameters I want to use. The generated output file will be in QTEMP. I execute the CL statement by using the same key combination I use to execute other statements. In my sessions that is Ctrl-r.

Line 2: The OBJECT_STATISTICS table function gives me the same type of data as the DSPOBJD command. In this case I am only interested in objects in MYLIB and in only three columns:

1. OBJNAME:  Object's name
2. OBJTYPE:  Object's type
3. OBJATTRIBUTE:  Object type attribute

I am using the CREATE TABLE to output the results from OBJECT_STATISTICS into a Table in QTEMP.

I can see what these two statements generated using the OBJECT_STATISTICS table function again. This time I am interested in the following columns:

1. OBJNAME:  Object's system name
2. OBJLONGNAME:  Object's long SQL name
3. OBJTYPE:  Object's type
4. OBJATTRIBUTE:  Object type attribute
5. SQL_OBJECT_TYPE:  SQL object type

SELECT OBJNAME,OBJLONGNAME,OBJTYPE,OBJATTRIBUTE,SQL_OBJECT_TYPE FROM TABLE(QSYS2.OBJECT_STATISTICS('QTEMP','ALL')) ;

The results surprised me as there is another Table in the results I did not create:

SQL_OBJECT OBJNAME OBJLONGNAME OBJTYPE OBJATTRIBUTE _TYPE ---------- ------------------ ------- ------------ ---------- OBJEC00001 OBJECT_LIST *FILE PF TABLE QSQ_S00001 QSQ_SQL_VAR_VALUES *FILE PF TABLE WDSPOBJD WDSPOBJD *FILE PF

Whenever I start an ACS "Run SQL Scripts" session a QSQ_SQL_VAR_VALUES Table is created in QTEMP automatically. What kind of information does it contain?

SELECT * FROM QTEMP.QSQ_SQL_VAR_VALUES ; SQL_ VARIABLE VARIABLE VARIABLE IDENTITY _SCHEMA _NAME _VALUES -------- -------- ------------- ----------------------------------- 1 SYSIBM CLIENT_HOST 'CEP-99-99-99-99.AUSTIN.RES.RR.COM' 2 SYSIBM CLIENT_IPADDR '99.99.99.99' 3 SYSIBM CLIENT_PORT 99999

These are three of the built in global SQL variables that I can use in other SQL statements.

Returning to my original purpose, listing columns from Tables in QTEMP.

Now I can use the SYSCOLUMNS_SESSION to get a list of columns from the Tables.

01 SELECT TABLE_NAME AS "Table", 02 SYSTEM_TABLE_NAME AS "Sys name", 03 COLUMN_NAME AS "Column", 04 SYSTEM_COLUMN_NAME AS "Sys name", 05 DATA_TYPE AS "SQL type", 06 DDS_TYPE AS "DDS", 07 LENGTH AS "Length", 08 NUMERIC_SCALE AS "Dec", 09 IS_NULLABLE AS "Alw null", 10 IS_IDENTITY AS "Identity" 11 FROM QSYS2.SYSCOLUMNS2_SESSION 12 ORDER BY 1,ORDINAL_POSITION ;

I think all of the columns' names explain their contents.

Line 6: DDS_TYPE is one of those columns that is not in SYSCOLUMNS.

Line 12: The number in the ORDER BY denotes the column in the results. In this example I am sorting first by the first column in the results, TABLE_NAME, and then by the ORDINAL_POSITION, which is a derived number that denotes the column's position in the Table.

The results are:

Table Sys name Column Sys name ------------------ ---------- --------------- ---------- OBJECT_LIST OBJEC00001 OBJNAME OBJNAME OBJECT_LIST OBJEC00001 OBJTYPE OBJTYPE OBJECT_LIST OBJEC00001 OBJATTRIBUTE OBJAT00001 QSQ_SQL_VAR_VALUES QSQ_S00001 SQL_IDENTITY SQL_I00001 QSQ_SQL_VAR_VALUES QSQ_S00001 VARIABLE_SCHEMA VARSCHEMA QSQ_SQL_VAR_VALUES QSQ_S00001 VARIABLE_NAME VARNAME QSQ_SQL_VAR_VALUES QSQ_S00001 VARIABLE_VALUES QQDBCLOB1 SQL type DDS Length Dec Alw null Identity -------- --- ---------- ------ -------- -------- VARCHAR A 10 Y NO VARCHAR A 8 Y NO VARCHAR A 10 Y NO INTEGER B 4 0 N YES VARCHAR A 128 Y NO VARCHAR A 128 Y NO DBCLOB 3 1073741823 Y NO

The first thing I noticed was that the WDSPPOBJD file is missing. As it is a physical file and not a SQL object its information is not included in the View.

I learned two things writing this post that I will find useful. Firstly, I should use SYSCOLUMNS2 rather than SYSCOLUMNS. Secondly, I can now see the information from Tables in QTEMP using SYSCOLUMNS2_SESSION.

You can learn more about this from the IBM website:

• SYSCOLUMNS2 View
• SYSCOLUMNS2_SESSION View

 

This article was written for IBM i 7.4 TR3 and 7.3 TR9.