What’s That Name?
October 17, 2012 Paul Tuohy
|
The basis for this article comes from a brief conversation I had over coffee, while recently presenting an Introduction to SQL course. It went along these lines: Tom: “In Run SQL Scripts, how can I get a list of the fields in a file?” Me: “You mean the columns in a table or view.” Tom: “OK. In Run SQL Scripts, how can I get a list of the columns in a table or a view?” Me: “Just do a SELECT * and the column names are shown at the top of the columns.” Tom: “Nope. I took the option to display labels instead of names, because the names are fairly meaningless. Is there some way I can just get a good old record layout?” Tom was stuck between a rock and a hard place. He could have “meaningful” text for the column headings but have no idea what the column names were, or he could have “meaningless” column names as headings and do his best to guess what the columns actually were. Lots of our old DDS defined tables have fairly incomprehensible column names; sometimes six characters long and the first two are a unique identifier (an example in a moment). By the way, this is one of the reasons the rest of the world does not believe us when we say we have a relational database on i! So, how do you go about getting a “record layout” in Run SQL Scripts? Getting the Layout All of the information about all of the database objects on the system is kept in a number of database tables in QSYS and QSYS2. These tables are sometimes referred to as the “System Cross Reference Database”. The table we are particularly interested in is QSYS/QADBIFLD. Running the SQL SELECT statement, shown in the following code, would give us the result set shown in Figure 1–our required record layout. Don’t worry, you do not have to remember this SELECT statement, it will be wrapped in a stored procedure in just a minute.
select dbipos as pos,
dbifld as name,
coalesce(trim(dbitxt), trim(dbihdg)) as text,
dbiitp as dds_type,
dbityp as sql_type,
dbifln as length,
dbinsc as dec,
trim(dbilfl) as long_name
from qsys/qadbifld
where dbilib = 'RPGINTRO' and dbifil = 'CUSTOMER'
order by pos;
The names of the columns in QADBIFLD are a perfect example of the difficult column names I mentioned earlier. Whereas you might have guessed the meaning of DBIFLD, DBITXT, and DBIHDG, would you have guessed that DBIITP was the DDS data type and DBITYP was the SQL data type? In the first piece of code above, the coalesce of the columns DBITXT and DBIHDG is to ensure that some sort of text description is returned; you will get the column headings if a text description has not been provided. Put It In A Procedure Trying to remember the SQL statement in the code is just too much work! Let’s wrap it in a stored procedure called GET_LAYOUT, as shown in below in the next bit of code. Use SET SCHEMA to specify the name of the schema where the stored procedure is to be stored or qualify the name on the CREATE PROCEDURE command. To make life easier by not having to qualify the stored procedure name every time you call it, this schema should be one that is in the schema list used in Run SQL Scripts. The stored procedure (shown below) accepts two parameters (schema name and table/view name), and returns a result set, as per Figure 1. The WHERE clause was changed to use the UPPER function on the two parameter fields–no need to force the entry of names in uppercase.
CREATE PROCEDURE GET_LAYOUT (
IN SCHEMA_NAME CHAR(10) ,
IN TABLE_NAME CHAR(10) )
DYNAMIC RESULT SETS 1
LANGUAGE SQL
SPECIFIC GET_LAYOUT
NOT DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
PROGRAM TYPE SUB
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *NONE ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = *NONE ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
SRTSEQ = *HEX
BEGIN
DECLARE C1 CURSOR FOR
select dbipos as pos,
dbifld as name,
coalesce(trim(dbitxt), trim(dbihdg)) as text,
dbiitp as dds_type,
dbityp as sql_type,
dbifln as length,
dbinsc as dec,
trim(dbilfl) as long_name
from qsys/qadbifld
where dbilib = upper(SCHEMA_NAME) and
dbifil = upper(TABLE_NAME)
order by pos
for read only ;
OPEN C1 ;
SET RESULT SETS CURSOR C1 ;
END ;
Now, to get our “record layout” in Run SQL Scripts, all we have to do is call the stored procedure as shown in the following final piece of code, and we end up with the same result set shown above.
call get_layout('rpgintro', 'customer') ;
Errata Taking the option to Display Results in a Separate Window (from the Options menu), prior to calling the stored procedure, means you can have the layout in a window to one side while you construct your required SQL statement. In Run SQL Scripts, you can choose the Header for result columns (column names or column labels), by selecting Connection→JDBC Settings from the menu bar and selecting the Other tab. Have a look at all the information available in QADBIFLD, some of it may be of interest. As might some of the other QADB* tables. QSYS2/SYSCOLUMNS is a view of the information in QADBIFLD (and QADBXSFLD) with more meaningful column names. But not all columns (e.g., DBIITP) are represented. A schema that is created using the CREATE SCHEMA statement will contain views of the System Cross Reference Database with a preselection for the schema. For instance, the views only contain information for database objects in the schema. I hope you find some use for this tip in your ever-growing toolbox! Paul Tuohy is CEO of ComCon, an iSeries consulting company, and is one of the co-founders of System i Developer, which hosts the RPG & DB2 Summit conferences. He is an award-winning speaker who also speaks regularly at COMMON conferences, and is the author of “Re-engineering RPG Legacy Applications,” “The Programmers Guide to iSeries Navigator,” and the self-study course called “iSeries Navigator for Programmers.” Send your questions or comments for Paul to Ted Holt via the IT Jungle Contact page. RELATED STORIES SQL Conference Puts Spotlight on IBM i
|
