Are You…,
Struggling with database replication or migration?
Then…,
Say goodbye to database management headaches and discover how easy it is to retrieve DDL sources in IBM i.
Introduction
With modernization in IBM i and our constant drive to attain an immaculately performing system, we are rapidly moving towards rigorous utilization of SQL in IBM i object creation.
Whether it’s a table, trigger, function, program/module, constraints, permissions, or indexes; we create these objects with SQL to keep up with IBM i standards.
You may have wondered why we should use SQL to create new objects. The answer is quite straightforward – for the optimized drive of the OS resources and the ability to fine-tune the performance, when necessary, with new system developments.
Due to robust version control tools, and the fact that the source code is kept out of reach of all users, clients tend to keep only the objects in production rather than the source. In addition, objects other than programs/modules, procedures, or functions do not provide a source listing option apart from debugging.
Therefore, IBM i has a procedure to retrieve the definition of these SQL objects for a better understanding. The information provided in this section is extremely useful in understanding the object and its usage during the impact analysis or root cause analysis of any issue that may arise.
GENERATE_SQL_OBJECTS
In the QSYS2 library, this procedure generates a definition (DDL source) of an SQL object that can be used to recreate the object or check its inherent characteristics.
Users can select any library and source physical file of their choice to return the results to a pre-existing source file member.
This procedure requires mandatory input of a table name and schema of that table which must contain the following three columns as listed:
OBJECT_SCHEMA which is a VARCHAR-type column of length 258
- OBJECT_NAME which is a VARCHAR-type column of length 258
- SQL_OBJECT_TYPE which is a CHAR-type column of length 10
These mandatory columns are required on the input table to adhere to the input parameter of the background running API QSQGNDDL, which fetches the result set.
Let’s look at how to generate SQL DDLs for required SQL objects
- First, we will create an input table to hold the SQL object name and object types for which we need to generate DDL. Assume the input table to be in QTEMP, named INPUTTBL.
CREATE TABLE QTEMP.INPUTTBL(
OBJECT_SCHEMA VARCHAR(258),
OBJECT_NAME VARCHAR(258),
SQL_OBJECT_TYPE CHAR(10))
- The next step is to insert information about the SQL objects for which the DDL source is to be generated into the input table. In this example, we will generate the DDL source of an SQL table that we previously created in the QTEMP library, named TRS, which has two columns, ID, and NAME.
INSERT INTO QTEMP.INPUTTBL VALUES
(‘QTEMP’, ‘TRS’, ‘TABLE’)
Likewise, you can insert as many object names of SQL for which you require the DDL source.
- After the entries are created in our input table, we can call our procedure by mentioning the output source member.
A result holding source member TDDLSRC has been created in the PIOLIB library, under the source physical file QDDSSRC.
CALL QSYS2.GENERATE_SQL_OBJECTS(
SYSTEM_TABLE_NAME=>’INORDER’,
SYSTEM_TABLE_SCHEMA=>’QTEMP’,
DATABASE_SOURCE_FILE_NAME=>’QDDSSRC’,
DATABASE_SOURCE_FILE_LIBRARY_NAME=>’PIOLIB’,
DATABASE_SOURCE_FILE_MEMBER => ‘TDDLSRC’
)
Note: If DATABASE_SOURCE_FILE_MEMBER is not specified, Q_GENSQOBJ will be used.
We have some additional parameters as well for the procedure call. They are as follows:
- STATEMENT_FORMATTING_OPTION: If ‘0’, then no additional formatting. If ‘1’, additional end-of-line and tab characters are added to the generated SQL statements.
- COMMENT_OPTION: If ‘0’, comment SQL statements should not be generated. If ‘1’, comments are generated as well for TABLE or VIEW. The default is ‘1’.
- LABEL_OPTION: If ‘0’, labels are not generated. If ‘1’, labels are generated for object type TABLE or VIEW. The default is ‘1’.
- CCSID_OPTION: If ‘0’, ccsid attributes are not generated. If ‘1’, ccsid attributes are generated. The default is ‘1’.
- By executing the above call statement in the interactive SQL screen, we can view the contents of the result-holding source member. Here is a snippet of the DDL source we generated for our SQL object:
Restrictions
In SQL, only a limited variety of objects can be generated as DDL Sources. Below is a list of them:
- ALIAS: Object is an SQL alias
- CONSTRAINT: object type is a constraint
- FUNCTION: Object is an SQL function
- INDEX: The object is an SQL index
- MASK: The object is an SQL column mask
- PERMISSION: The object is an SQL row permission
- PROCEDURE: Object is an SQL procedure
- SCHEMA: Object is an SQL schema
- SEQUENCE: The object is an SQL sequence
- TABLE: The object is an SQL table or physical file
- TRIGGER: Object attribute is a trigger
- TYPE: Object is an SQL type
- VARIABLE: Object is an SQL global variable
- VIEW: The object is an SQL view or logical file
- XSR: The object is an XML schema repository object.
It follows the below order to generate the DDL source of SQL objects in the given output source file member:
- Schemas
- Types
- Sequences
- Aliases
- Non-MQT tables and any constraints and indexes on those tables
- Functions
- Procedures
- Variables
- Views, DDS-created logical files MQTs and any constraints and indexes on those tables
- Triggers
- Masks
- Permissions
- XSR objects
Conclusion
In IBM i, we can seamlessly utilize SQL to regenerate the DDL source of the mentioned object types. Whether you prefer using SQL commands, web-based tools like IBM Navigator for i, or desktop applications like IBM i Access Client Solutions, IBM i offers robust solutions for generating and managing DDL. It is easy to replicate, maintain, and document your database schema by leveraging these tools.
