Wednesday, March 17, 2021

View columns for SQL tables, etc. located in QTEMP

columns from sql objects 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:

 

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

7 comments:

  1. Looks like QTEMP files createdvis STRSQL dont get registered in SESSIION,
    Is this expected or am i missing soemthing?

    ReplyDelete
    Replies
    1. I just created a SQL table in QTEMP using STRSQL. I can see its information using the SYSCOLUMNS2_SESSION view.

      Could it be that the partition you are using is not at the necessary IBM i release and Technology Refresh?

      Delete
  2. Can this be used to look at qtemp tables in another's' job?

    ReplyDelete
  3. Very interesting

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.