DB2 For i Odds And Ends
February 12, 2014 Michael Sansoterra
|
IBM i 7.1 and its various incremental releases have introduced many great new features in DB2 for i. Many of the features have been covered in Four Hundred Guru tips. This tip will cover some of the less exciting, yet important features that may have gone unnoticed. Best of all, many of them are even available in IBM i 6.1. Specify System PROGRAM NAME Keyword When Creating An SQL Trigger To Prevent Naming Conflicts For System Names Consider the following CREATE TRIGGER statement that features a long SQL name:
CREATE OR REPLACE TRIGGER ADVWORKS08.UpdateSalesOrderHeaderModifedDate
AFTER UPDATE ON ADVWORKS08.SalesOrderDetail
REFERENCING NEW_TABLE AS ChangedOrderDetail
FOR EACH STATEMENT MODE DB2SQL
PROGRAM NAME SALESHDRT1
SET OPTION USRPRF=*OWNER, DATFMT=*ISO
BEGIN ATOMIC
UPDATE ADVWORKS08.SalesOrderHeader
SET ModifiedDate=CURRENT_TIMESTAMP
WHERE SalesOrderId IN
(SELECT SalesOrderId FROM ChangedOrderDetail);
END
When DB2 creates this trigger’s CLE program object, what will the short (or system) name for the trigger be? The answer is: It depends. It could be assigned a name of UPDAT00001 or UPDAT00002, etc. The naming assignment varies depending on what system object names are already in the schema. Moreover, when the same trigger is created in multiple schemas, it could be assigned a different system name in each schema due to factors such as pre-existing objects in the schema, order of object creation in the schema. This can lead to inconsistencies where in one schema a journal entry might indicate a row was changed by trigger program UPDAT00001 and in another schema a journal entry might record the same trigger name as UPDAT00003. Using the PROGRAM NAME clause, a developer can now assign a system (a.k.a. program) name (as shown in the example above) in addition to the long name for the trigger. This has two benefits: naming consistency across schemas and partitions, and being able to assign an intelligible system name. This feature is available in IBM i 7.1starting with DB2 for i group PTF Level 4 and in IBM i 6.1 starting with PTF Group level 19. You can find more information here on IBM‘s website. Specify System Names For Tables, Views, And Indexes This feature tackles a similar problem as managing a long SQL name and a system name can be a pain in the neck. Voilà ! The new FOR SYSTEM NAME clause can be used to specify both names at object creation: CREATE TABLE DATALIB.WAREHOUSE_LOCATIONS FOR SYSTEM NAME WHSLOC ( WHS_ID SMALLINT NOT NULL, LOC_ID SMALLINT NOT NULL, LOC_NAME VARCHAR(32) NOT NULL) When the WAREHOUSE_LOCATIONS table is built, the system name will be WHSLOC. This feature eliminates the annoying need to issue a Rename Object (RNMOBJ) i OS command after creating an SQL object with a long name. FOR SYSTEM NAME can also be used with CREATE INDEX, CREATE VIEW, and DECLARE GLOBAL TEMPORARY TABLE statements. This feature is available in IBM i 7.1starting with DB2 for i group PTF Level 23. You can find more information here on IBM’s website. Use GET DIAGNOSTICS To Return The Row Count For A CREATE TABLE WITH DATA Statement SQL developers are accustomed to finding out how many rows were affected by a data modification statement (DELETE, INSERT, UPDATE, REFRESH, or MERGE) using the GET DIAGNOSTICS statement: DECLARE RowsAffected DEC(31,0); DELETE FROM CustomerHistory WHERE CustomerId=31000; GET DIAGNOSTICS RowsAffected = ROW_COUNT; Now, the CREATE TABLE WITH DATA or DECLARE GLOBAL TEMPORARY TABLE WITH DATA statements will also update the row count information that GET DIAGNOSTICS provides.
DECLARE RowsAffected DEC(31,0);
DECLARE GLOBAL TEMPORARY TABLE OBJECT_LIST AS (
SELECT *
FROM QSYS2.TABLES
WHERE TABLE_SCHEMA='DATALIB_51'
) WITH DATA;
GET DIAGNOSTICS RowsAffected = ROW_COUNT;
IF RowsAffected=0 THEN
SIGNAL SQLSTATE '38001'
SET MESSAGE_TEXT='Data tables have not been installed';
END IF;
/* Other processing goes here… */
When using embedded SQL, the row count value for this operation (and other data modification statements) will be placed in field SqlErrD3 within the SQL communication area (SQLCA) data structure. This feature is available in IBM i 7.1starting with DB2 for i group PTF Level 11 and in IBM i 6.1 starting with PTF Group level 21. You can find more information here on IBM’s website. QSYS2.QCMDEXC Procedure No Longer Requires A Command Length This is a useful feature. I was so used to defining my own wrapper or sticking with the ugly default in QSYS, I didn’t even realize IBM already provided a stored procedure wrapper for QCMDEXC in the QSYS2 schema! I often invoked a command from SQL the hard way:
CALL QSYS.QCMDEXC('SBMJOB ….',000000100.00000);
Now, things are as simple as they should be as you no longer have to calculate and supply a length:
CALL QSYS2.QCMDEXC ('DSPJOB OUTPUT(*PRINT) OPTION(*JOBLOG)');
This procedure definition has been overloaded with multiple parameter signatures so that existing code that specifies the length as a second parameter will continue to work. This feature is available in IBM i 7.1starting with DB2 for i group PTF Level 25 and in IBM i 6.1 starting with PTF Group level 30. You can find more information here on IBM’s website. Allow Unqualified External Names In SQL Wrappers That Reference A Service Program It is a very popular practice to publish ILE service program subprocedures with SQL using external wrappers for stored procedures and functions. However, one irritating facet of doing this has been that a service program had to be qualified with a library name. Dealing with this situation usually involved issuing a CREATE PROCEDURE or CREATE FUNCTION statement multiple times (for example, one to reference a test lib service program and another for a production lib copy.) Starting in IBM i 7.1, the EXTERNAL NAME option can reference an unqualified service program name. Consider the following CREATE FUNCTION (external scalar) statement that references RPG service program DATESRVPGM and subprocedure CVTCYMDDATE: CREATE OR REPLACE FUNCTION Lib/CvtCYMDDate (CYMDDate NUMERIC(7,0)) RETURNS DATE LANGUAGE RPGLE EXTERNAL NAME DATESRVPGM(CVTCYMDDATE) DETERMINISTIC RETURNS NULL ON NULL INPUT PARAMETER STYLE GENERAL NO SQL NOT FENCED When the service program name is unqualified as shown above, DB2 will search for the service program using the library list. Be careful, the function invocation will fail if the service program is not in the library list: SELECT CvtCYMDDate(1140101) FROM SYSIBM/SYSDUMMY1 Until now, if DB2 could find the routine definition it would not need the service program in the library list because DB2 always knew the library where the service program was located. If you decide to recreate your routine definitions with an unqualified service program name, make sure the service program will be available in each SQL job’s library list or you may wind up with an unexpected error. This feature is available in the base version of IBM i 7.1and in IBM i 6.1 starting with PTF Group level 8. Allow Java Routines To Use The System Library List This is one I wish I had years ago! When writing a Java function or procedure that accesses the local DB2 for i database, these routines can make direct use of the host database connection. However, the naming convention attribute for this “default” connection was always the *SQL naming convention. Therefore table and view references within the code had to be qualified. The DB2Connection object has a new method, called setUseSystemNaming, that can be used to allow the database connection to use the system naming convention (and the accompanying library list search benefits.) Here is the brief sample code IBM published. Note the differences depending on which parameter style is used to invoke the Java routine.
Parameter style DB2GENERAL:
DB2Connection connection = (DB2Connection) getConnection();
connection.setUseSystemNaming(true);
....
.... do work using the connection, where system naming should be used
....
connection.setUseSystemNaming(false);
Parameter style JAVA:
DB2Connection
connection = (DB2Connection)
DriverManager.getConnection("jdbc:default:connection");
connection.setUseSystemNaming(true);
....
.... do work using the connection, where system naming should be used
....
connection.setUseSystemNaming(false);
The notes from IBM also warn that when finished, the setUseSystemNaming should be used to reset the value to false before exiting the routine, otherwise unpredictable results may occur in later Java routine executions. This feature is available in IBM i 7.1starting with DB2 for i group PTF Level 15. You can find more information here on IBM’s website. Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. Send your questions or comments for Mike via the IT Jungle Contact page.
|
