“Compile” SQL objects into desired library – 1

Last Updated on 20 October 2019 by Roberto De Pedrini

The SQL data definition language (DDL) allows you to create a number of objects, some of them are comparable to traditional IBM i objects (eg tables, indexes, aliases), others are instead specific to DB2 (functions, procedures , etc..). These objects are generally created from scripts stored in a source member or in IFS stream files and then processed by the RUNSQLSTM command.
However, this command has no “Object Library” parameter in which to specify the destination for the object being created (as in standard compilation commands). The default object library is the job’s current library (*CURLIB); it is possible to indicate the destination library directly in the script’s source, but this is certainly not recommended… just think of the case in which we need to distribute the same object in multiple libraries; with hard-coded libraries we would be forced to create a script for each library (or to modify the script each time changing the library name). Not really an efficient practice.
But the RUNSQLSTM command has a parameter that goes quite unnoticed, and provides the solution: DFTRDBCOL (Default collection). This parameter allows you to specify the library (or rather, the schema) to be applied to all the unqualified objects that appear in the script to be executed.
Example:

RUNSQLSTM SRCFILE(MDUCA1/QSQLSRC) SRCMBR(TEST001) DFTRDBCOL(MDUCA1)

Thanks to this feature, it is possible to build a PDM option or a custom RDi command to “compile” SQL scripts:

RUNSQLSTM SRCFILE(&L/&F) SRCMBR(&N) COMMIT(*NONE) DFTRDBCOL(&L)

SQL scripts can also be stored in IFS files, the RUNSQLSTM command has indeed a special parameter (SRCSTMF) in which to specify the file containing the script, as an alternative to the classic source file member.

RUNSQLSTM SRCSTMF(/home/MDUCA/QSQLSRC/TEST001.sqltable) COMMIT(*NONE) DFTRDBCOL(MDUCA1)

Here we provide XCRTSQL, a tiny command that interfaces RUNSQLSTM asking for the most common parameters, it can be used as a “compilation” command for all objects to be built from SQL scripts. The command allows to specify in a “traditional” way the destination library of the object, the source (library/file/member or IFS file) and the destination release in order to manage some features of SQL commands that are available only from specific IBM i versions.
When managing SQL sources in classic source file members, it is good practice to use a member type that identifies the content; I personally adopt this standard:

SQLTABLE 	Tables
SQLVIEW		View
SQLINDEX	Indexes
SQLUDF		Functions (UDF, UDTF)
SQLPROC		Procedures
SQLTRG		Triggers
SQLALIAS	Alias

The CL processing program checks that the member type is one of this list, a generic member type (“SQL”) is also accepted. At the end of the “compilation” a message is issued with the result. For file source members, the MARGIN parameter is also set to the correct value based on the size of the source file.
Below is the GitHub link to download the sources, which of course everyone can customize at will:

https://github.com/MD2706GIT/XCRTSQL

Warning: the DFTRDBCOL parameter adopts the library indicated for all the unqualified objects of the script; so should the script contain objects stored in multiple libraries, this approach does not work, or may lead to unwanted results. Let’s think for example of a VIEW that brings together data from tables stored in different libraries. So in this case, there’s nothing we can do? Do we have to specify all the libraries in the source?
Before giving up, a solution can be found anyway… we’ll see it in the next episode!

Documentation:
RUNSQLSTM command: https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_74/cl/runsqlstm.htm

Related Posts

DB2 for i SQL – String Manipulation – POSSTR-LOCATE-LOCATE_IN_STRING (EN)

Introduction Often, in our applications, we need to work with text strings, and DB2 SQL can come in very useful Read more

DB2 for i – FAQ & Howtos (EN)

DB2 Database and SQL ... maybe the most important things on IBM i platform: here's a collection of FAQs, tips Read more

IBM i 7.4 Announcement (En)

Comes directly with the Easter egg this IBM announcement for the news of the IBM i 7.4 version, iNext version Read more

Generated Always Columns (EN)

Introduction "Generated Always Column": are columns, table fields, filled by DB2 engine: something like columns with a default value but Read more