Online Rolling Application Upgrades

Motivation

Imagine you are running a website like online banking. 

Imagine further you want to get as close to zero downtime as possible.

 

So you will invest into DB2 pureScale, HADR, or perhaps you choose a replication solution.

All these capabilities will get you close, protecting you against unplanned outages as well as outages related to hardware and OS upgrades. 

 

What none of them do is helping you deploy application upgrades!

Traditionally when you upgrade an application you will take a planned outage. 
Then you tear down the existing application objects on the database and then rebuild the new version.

This can take some time.

 

A customer I am working with is however asking to do scheduled application upgrades once a quarter - with zero impact to their online presence.

The lay of the land

Let's first take a look at the topology of such an app:

1.    There is the client side application which is most likely written in Java

2.    There are multiple application servers (Websphere or Weblogic most likely)

3.    There is server side logic present in

o    Modules

o    PL/SQL Packages

o    Functions

o    Procedures

o    Types

o    Variables

This application is ultimately running on a database schema consisting of

·        Tables

·        Views

·        Indexes

·        Sequences

·        Triggers

·        .. and data :-)

In a serious development environment the application source will be tightly controlled  in some version control system with methods to deploy on a test system, integration system and production system.

 

Upgrading the client side part of the application is rather straight forward. 
All it takes is a rolling upgrade through the web servers.
 One web server at a time is taken offline, upgraded and then re-integrated.
But how do we upgrade the server side application? DB2 does not support explicit versioning of modules and the likes.   

 

Making it happen

Luckily DB2 supports something else which does the job very nicely as long as some basic best practices are followed. 
DB2 resolves application objects by PATH.

In DB2 you can have objects with the same name in multiple schemata. 
Using the session level PATH registry variable you can control which application sees which objects.

There is no hard limit on how many schemata can be used.  
Unlike with other DBMS such as Oracle, there is no also security concern because a schema is orthogonal to a user. 
Therefor the same set of users can own objects in many schemata.

 

So let's establish a few ground rules for our application:

·        The source code of the objects must not use explicit qualifiers when referencing another application objects.
Good:

  SET x = foo();

Bad:

  SET x = myapp.foo();

·        This includes the name of the application object itself:
Good:

  CREATE OR REPLACE foo() ...

Bad:

  CREATE OR REPLACE myapp.foo()...

·        When referencing the db schema objects a schema name must be used unless a public synonym exists
Good:

  SELECT * FROM myschema.t;

Bad:

  SELECT * FROM t;

·        The schemata which are holding tables, views, sequences must be distinct from the application schema.
Good:
   Routine myapp.foo() and table myschema.t
Bad:
   Routine myschema.foo() and table myschema.t
 

·        Avoid overloading procedures and functions by name across multiple schemata, unless they are in modules or PL/SQL packages
Good:
  Procedures hr.hire() and finance.income()
Bad:
  Procedures hr.add() and finance.add()

With these rules we can deploy the application into any set of schemata simply by setting the CURRENT SCHEMA and CURRENT PATH registers before executing the DDL.

The client application then uses the same CURRENT PATH to find the application.

From here it's just a small step and we are there.
We just have to come up with a naming convention to version application schema names and a means to manage which application server connects to which version.

For naming we can trail the version number to the original schema name  HR_1_1 and FINANCE_2_3. The names may not be pretty, but we will never see them in the source code.
As for managing the mapping between application servers, versions and paths the log-on procedure I introduced in my very first blog-post provides the perfect intercept point.

 

This was a lot of theory. Time to build a real example.

Example

 Of course we won't use a three tier architecture here. That would blow my modest set-up.
While you can choose any number of schemata for your application we'll also stay with one schema here.

 

First we need to install a "version management system". 
You will find the source code at the end of this blog post. 
If you store it as vms.sql in the current directory you can execute it from CLPPlus like this:

@@vms

The DB schema:

SET SCHEMA = DATA;

CREATE TABLE emp(id  INTEGER NOT NULL PRIMARY KEY,
name VARCHAR(20),
salary INTEGER);

CREATE SEQUENCE empid;

The application in our version control system represented by a file:

app_v1.sql

CREATE OR REPLACE PROCEDURE hire_emp(OUT id     ANCHOR TO data.emp.id,
IN  name   ANCHOR TO data.emp.name,
IN  salary ANCHOR TO data.emp.salary)
BEGIN  SET id = NEXT VALUE FOR data.empid;
INSERT INTO data.emp VALUES(id, name, salary);
END;
/

CREATE OR REPLACE PROCEDURE fire_emp(IN name ANCHOR TO data.emp.name)
BEGIN
DELETE FROM data.emp WHERE fire_emp.name = name;
END;
/

Now we install the application and assign the application server by host name.
Note that all names are case sensitive including the host name.

Install App 1.0

SET SCHEMA = APP_1_0;
SET PATH = SYSTEM PATH, APP_1_0;
@@app_v1.sql
CALL version.register('App 1.0', 'DATA', CURRENT_PATH);
CALL server.register('srielau', 'App 1.0');
COMMIT;

Run App 1.0

 Time to fire up our application in a separate CLPPlus window.
When we connect the connect procedure will match the host name with the application version.
it then sets the PATH to include "APP_1_0" and the SCHEMA to "DATA".

BEGIN
DECLARE id ANCHOR TO data.emp.id;
LOOP
CALL hire_emp(id, 'John', 20000);
COMMIT;
CALL fire_emp('John');
COMMIT;
END LOOP;
END;
/

While our application is being exercised we want to do an upgrade. 

Firing employees by name seems like a recipe for disaster in case of duplicates. 
Instead we should use employee ids since they are primary keys.

Our server side application logic will now look like this:

app_v1.1.sql:

CREATE PROCEDURE hire_emp(OUT id     ANCHOR TO data.emp.id,
IN  name   ANCHOR TO data.emp.name,
IN  salary ANCHOR TO data.emp.salary)
BEGIN
SET id = NEXT VALUE FOR data.empid;
INSERT INTO data.emp VALUES(id, name, salary);
END;
/

CREATE PROCEDURE fire_emp(IN id ANCHOR TO data.emp.name)
BEGIN
DELETE FROM data.emp WHERE fire_emp.id = id;
END;
/

Install App 1.1

SET SCHEMA = APP_1_1;
SET PATH = SYSTEM PATH, APP_1_1;
@@app_v1.1.sql
CALL version.register('App 1.1', 'DATA', CURRENT PATH);
COMMIT;

To ensure that the new application version doesn't "cross talk" I have provided a small function that flags any dependencies of the application outside of an approved list:
We run that to ensure the new application version is well behaved:

SET VERSION.SOURCE = ARRAY['APP_1_1'];

SET VERSION.EXCEPT = ARRAY['DATA'];

SELECT COUNT(*) FROM UNNEST(VERSION.LIST_EXTERNAL_DEPENDENCIES());
1
-----------
0

Production grade rolling upgrade

Normally you now perform the following steps:

1.    Drain an application server by disallowing new client connections

2.    Update the application server

3.    Update the application version for the application server's host name using the SERVER.UPDATE procedure

4.    re-integrate the application server.

 

"Laptop grade" rolling upgrade

 Since I'm doing this experiment on my laptop I only have one application server.
So I will update the version while my one CLPPlus shell is still executing App 1.0.
Then I fire up a new CLPPlus shell with a new connection to demonstrate both application versions working in parallel.

 

Upgrade application server

CALL server.update('srielau', 'App 1.1');
COMMIT;

Run App 1.1

BEGIN
DECLARE id ANCHOR TO data.emp.id;
LOOP
CALL hire_emp(id, 'Jeremy', 30000);
COMMIT;
CALL fire_emp(id);
COMMIT;
END LOOP;
END;
/

When I disconnect the original CLPPlus application I need to run the new client application of course.

The snapshot below shows:

1.    Top-Left the first connection running App.1.0

2.    Top-Right the second connection running App 1.1

3.    Bottom left the console used for all administration

4.    Both apps are firing away concurrently saturating the laptop.

Rolling upgrade rules

What is important to note here is that:

·        We deploy a completely new version of the application even though we just changed one of the two procedures. 

·        The deployment was 100% online. It did not affect the running of the first version beyond CPU and IO resources to compile and deploy

·        If you want to subdivide the application into multiple schemas that is fine, but keep in mind that the application of a given version will always use the same PATH.
Assume HR_1_3 invokes schema FINANCE_3_0.
 Upgrading FINANCE_3_0 to FINANCE_3_1 will only be visible to HR if you add a new version HR_1_4 invoking FINANCE_3_1 even if nothing changed in HR.

Both applications can work at the same time here. But you must design for that.
If one application, for example produces data which the other cannot ingest then there will be failures.
This implies that for a successful online "rolling upgrade" you may need to use two steps:

1.    One preparation step which merely enhances the application to cope with unexpected data.

2.    Once that preparation step is completely rolled out the extra function can be added. 
But again the new application version has to be tolerant of the previous version's effects on the database.

 

Version Management System

 I have written a simple set of routines in a VERSION module which manage the association of different PATH and SCHEMA settings to application versions. 
A second set of routines picks up these versions and associates them with host names which represent application servers. 
A connection procedure ensures the settings are applied accordingly based on a connection's host name. 
You can of course add additional properties to versions, such as isolation levels or user defined variables. 
You may also want to replace HOSTNAME with another identifier for the application server.
Lastly the code below supplies a routine which double checks that application source code is "pure". 
The goal is that there is no cross talk across versions. 
Here is a quick overview:

·        VERSION.REGISTER(VERSION VARCHAR(10), SCHEMA VARCHAR(128), PATH VARCHAR(2048))
 This procedure defines a version and sets its properties. The schema will be used by dynamic queries to resolve unqualified table, view and sequence names. The PATH will be used to resolve any routines, modules, types and variables.
 

·        VERSION.DEREGISTER(VERSION VARCHAR(10))
 This procedure removes a version. The version must not be used by any server. Note that removal of the version does not drop any objects.
 

·        VERSION.UPDATE(VERSION VARCHAR(10), SCHEMA VARCHAR(128), PATH VARCHAR(2048))
 This procedure updates an existing version to new properties. If SCHEMA is not specified or NULL it will remain unchanged. The same is true for PATH. The update will be visible to any new connection coming from an associated server.
 

·        VERSION.SOURCE SCHEMA_LIST
 This variable needs to be set to an array of schemata which make up an application version before invoking VERSION.LIST_EXTERNAL_DEPENDENCIES().
 

·        VERSION.EXCEPT SCHEMA_LIST
 This variable needs to be set to an array of schemata to be ignored by VERSION.LIST_EXTERNAL_DEPENDENCIES(). Typically this will be the list of schemata holding tables, views and sequences referenced within the application.
 

·        VERSION.LIST_EXTERNAL_DEPENDENCIES() RETURNS DEPENDENCY_LIST
 This scalar function returns and array of rows representing objects referenced by any object in a schema set in VERSION.SOURCE which is neither listed in VERSION.SOURCE nor VERSION.EXCEPT. That is the function lists any reference to an object outside of the application version. It should return an empty array. 
 Example:

·         SET VERSION.SOURCE = ARRAY['HR_1_5', 'FINANCE_2_1'];

·         SET VERSION.EXCEPT = ARRAY['ARCHIVE', 'ACTIVE'];

SELECT * FROM UNNEST(VERSION.LIST_EXTERNAL_REFERENCES());

·        SERVER.REGISTER(SERVERNAME VARCHAR(32), VERSION VARCHAR(10))
 This procedure registers an application server identified by the hostname and associates it with an application version.
 

·        SERVER.DEREGISTER(SERVERNAME VARCHAR(32))
 This procedure removes a server name for the list of known servers.
 

·        SERVER.UPDATE(SERVERNAME VARCHAR(32), VERSION VARCHAR(10))
 This procedure updates an application server identified by the hostname to a new application version. 
The next connection from this SERVERNAME will use the PATH and SCHEMA of the associated VERSION.

 

vms.sql

1.  SET SCHEMA = CONFIG; 
SET PATH = SYSTEM PATH, CONFIG; 
BEGIN 
  DECLARE CONTINUE HANDLER FOR SQLSTATE '42704' BEGIN END; 
  EXECUTE IMMEDIATE 'DROP TABLE APPSERVER'; 
  EXECUTE IMMEDIATE 'DROP TABLE APPVERSION'; 
END; 
/ 

CREATE TABLE APPSERVER(SERVERNAME VARCHAR(32) NOT NULL PRIMARY KEY, 
                       VERSION    VARCHAR(10) NOT NULL); 

CREATE TABLE APPVERSION(VERSION VARCHAR(10)   NOT NULL PRIMARY KEY, 
                        SCHEMA  VARCHAR(128)  NOT NULL, 
                        PATH    VARCHAR(2048) NOT NULL); 

ALTER TABLE APPSERVER ADD CONSTRAINT APPVERSION FOREIGN KEY (VERSION) 
                          REFERENCES APPVERSION ON DELETE RESTRICT ON UPDATE RESTRICT; 

CREATE OR REPLACE MODULE VERSION; 
/ 

CREATE OR REPLACE PUBLIC SYNONYM VERSION FOR MODULE VERSION;
/ 

ALTER MODULE VERSION PUBLISH PROCEDURE REGISTER(VERSION ANCHOR TO APPVERSION.VERSION, 
                                                SCHEMA  ANCHOR TO APPVERSION.SCHEMA, 
                                                PATH    ANCHOR TO APPVERSION.PATH); 
/ 

ALTER MODULE VERSION PUBLISH PROCEDURE DEREGISTER(VERSION ANCHOR TO APPVERSION.VERSION); 
/ 

ALTER MODULE VERSION PUBLISH PROCEDURE UPDATE(VERSION ANCHOR TO APPVERSION.VERSION, 
                                              SCHEMA  ANCHOR TO APPVERSION.SCHEMA DEFAULT (NULL), 
                                              PATH    ANCHOR TO APPVERSION.PATH   DEFAULT (NULL)); 
/ 

ALTER MODULE VERSION PUBLISH TYPE SCHEMA_LIST AS VARCHAR(128) ARRAY[]; 
/ 

ALTER MODULE VERSION PUBLISH TYPE DEPENDENCY AS ROW(FROM_SCHEMA VARCHAR(128), 
                                                    FROM_MODULE VARCHAR(128), 
                                                    FROM_OBJECT VARCHAR(128), 
                                                    FROM_TYPE   VARCHAR(20), 
                                                    TO_SCHEMA   VARCHAR(128), 
                                                    TO_MODULE   VARCHAR(128), 
                                                    TO_OBJECT   VARCHAR(128), 
                                                    TO_TYPE VARCHAR(20)); 
/ 

ALTER MODULE VERSION PUBLISH TYPE DEPENDENCY_LIST AS DEPENDENCY ARRAY[]; 
/ 

ALTER MODULE VERSION PUBLISH VARIABLE SOURCE SCHEMA_LIST; 
/ 

ALTER MODULE VERSION PUBLISH VARIABLE EXCEPT SCHEMA_LIST; 
/ 

ALTER MODULE VERSION PUBLISH FUNCTION LIST_EXTERNAL_DEPENDENCIES() RETURNS DEPENDENCY_LIST; 
/ 

CREATE OR REPLACE MODULE SERVER; 
/ 

CREATE OR REPLACE PUBLIC SYNONYM SERVER FOR MODULE SERVER; 
/ 

ALTER MODULE SERVER PUBLISH PROCEDURE REGISTER(SERVERNAME ANCHOR TO APPSERVER.SERVERNAME, 
                                               VERSION    ANCHOR TO APPSERVER.VERSION); 
/ 

ALTER MODULE SERVER PUBLISH PROCEDURE DEREGISTER(SERVERNAME ANCHOR TO APPSERVER.SERVERNAME); 
/ 

ALTER MODULE SERVER PUBLISH PROCEDURE UPDATE(SERVERNAME ANCHOR TO APPSERVER.SERVERNAME, 
                                             VERSION    ANCHOR TO APPSERVER.VERSION); 
/ 

ALTER MODULE VERSION ADD PROCEDURE REGISTER(VERSION ANCHOR TO APPVERSION.VERSION, 
                                            SCHEMA  ANCHOR TO APPVERSION.SCHEMA, 
                                            PATH    ANCHOR TO APPVERSION.PATH DEFAULT(NULL)) 
BEGIN 
  DECLARE EXIT HANDLER FOR SQLSTATE '23505' 
  BEGIN 
    DECLARE txt VARCHAR(70); 
    SET txt = 'Version: ''' || VERSION || ' already exists.'; 
    SIGNAL SQLSTATE '78000' SET MESSAGE_TEXT = txt; 
  END; 
  INSERT INTO APPVERSION VALUES(VERSION, SCHEMA, COALESCE(PATH, 'SYSTEM PATH, "' || SCHEMA || '"')); 
END; 
/ 

ALTER MODULE VERSION ADD PROCEDURE DEREGISTER(VERSION ANCHOR TO APPVERSION.VERSION) 
BEGIN 
  DECLARE EXIT HANDLER FOR SQLSTATE '02000' 
  BEGIN 
    DECLARE txt VARCHAR(1000); 
    SET txt = 'Version: ''' || VERSION || ''' not found.'; 
    SIGNAL SQLSTATE '78000' SET MESSAGE_TEXT = txt; 
  END; 
  DECLARE EXIT HANDLER FOR SQLSTATE '23001' 
  BEGIN 
    DECLARE txt VARCHAR(4000); 
    SELECT '''' || VERSION || ''' used by ' || '''' || LISTAGG(SERVERNAME) WITHIN GROUP (ORDER BY SERVERNAME) || '''' INTO txt 
      FROM APPSERVER WHERE VERSION = DEREGISTER.VERSION GROUP BY VERSION; 
    SIGNAL SQLSTATE '78000' SET MESSAGE_TEXT = txt; 
  END; 
  DELETE FROM APPVERSION WHERE VERSION = DEREGISTER.VERSION; 
END; 
/ 

ALTER MODULE VERSION ADD PROCEDURE UPDATE(VERSION ANCHOR TO APPVERSION.VERSION, 
                                          SCHEMA  ANCHOR TO APPVERSION.SCHEMA  DEFAULT (NULL), 
                                          PATH    ANCHOR TO APPVERSION.PATH    DEFAULT (NULL)) 
BEGIN
  DECLARE EXIT HANDLER FOR SQLSTATE '02000'
  BEGIN
    DECLARE txt VARCHAR(70);
    SET txt = 'Version: ''' || VERSION || ''' not found.';
    SIGNAL SQLSTATE '78000' SET MESSAGE_TEXT = txt;
  END;
  UPDATE APPVERSION SET SCHEMA = COALESCE(UPDATE.SCHEMA, SCHEMA), 
                        PATH   = COALESCE(UPDATE.PATH, PATH) 
   WHERE VERSION = UPDATE.VERSION; 
END; 
/ 

ALTER MODULE SERVER ADD PROCEDURE REGISTER(SERVERNAME ANCHOR TO APPSERVER.SERVERNAME, 
                                           VERSION    ANCHOR TO APPSERVER.VERSION) 
BEGIN 
  DECLARE EXIT HANDLER FOR SQLSTATE '23505' 
  BEGIN 
    DECLARE txt VARCHAR(70); 
    SET txt = 'Server: ''' || SERVERNAME || ' already exists.'; 
    SIGNAL SQLSTATE '78000' SET MESSAGE_TEXT = txt; 
  END; 
  DECLARE EXIT HANDLER FOR SQLSTATE '23503' 
  BEGIN
    DECLARE txt VARCHAR(70); 
    SET txt = 'Version: ''' || VERSION || ''' does not exist.'; 
    SIGNAL SQLSTATE '78000' SET MESSAGE_TEXT = txt; 
  END; 
  INSERT INTO APPSERVER VALUES(SERVERNAME, VERSION); 
END; 
/ 

ALTER MODULE SERVER ADD PROCEDURE DEREGISTER(SERVERNAME ANCHOR TO APPSERVER.SERVERNAME) 
BEGIN 
  DECLARE EXIT HANDLER FOR SQLSTATE '02000' 
  BEGIN 
    DECLARE txt VARCHAR(70); 
    SET txt = 'Server: ''' || SERVERNAME || ''' not found.'; 
    SIGNAL SQLSTATE '78000' SET MESSAGE_TEXT = txt; 
  END; 
  DELETE FROM APPSERVER WHERE SERVERNAME = DEREGISTER.SERVERNAME; 
END; 
/ 

ALTER MODULE SERVER ADD PROCEDURE UPDATE(SERVERNAME ANCHOR TO APPSERVER.SERVERNAME, 
                                         VERSION    ANCHOR TO APPSERVER.VERSION) 
BEGIN 
  DECLARE EXIT HANDLER FOR SQLSTATE '02000' 
  BEGIN 
    DECLARE txt VARCHAR(70); 
    SET txt = 'Server: ''' || SERVERNAME || ''' not found.'; 
    SIGNAL SQLSTATE '78000' SET MESSAGE_TEXT = txt; 
  END; 
  DECLARE EXIT HANDLER FOR SQLSTATE '23503' 
  BEGIN 
    DECLARE txt VARCHAR(70); 
    SET txt = 'Version: ''' || VERSION || ''' does not exist.'; 
    SIGNAL SQLSTATE '78000' SET MESSAGE_TEXT = txt; 
  END; 
  UPDATE APPSERVER SET VERSION = UPDATE.VERSION WHERE SERVERNAME = UPDATE.SERVERNAME; 
END; 
/ 

SET ENVVARSUBSTITUTION OFF; 
CREATE OR REPLACE PROCEDURE CONNECT_PROC() SPECIFIC CONNECT_PROC 
BEGIN 
  DECLARE STMTTXT VARCHAR(4000); 
  DECLARE CLIENT_HOSTNAME ANCHOR TO APPSERVER.SERVERNAME; 
  SELECT DETMETRICS.CLIENT_HOSTNAME INTO CLIENT_HOSTNAME 
    FROM TABLE(MON_GET_CONNECTION_DETAILS(MON_GET_APPLICATION_HANDLE(), -1)) AS CONNMETRICS, 
         XMLTABLE (XMLNAMESPACES( DEFAULT 'http://www.ibm.com/xmlns/prod/db2/mon'), 
                   '$detmetric/db2_connection' PASSING XMLPARSE(DOCUMENT CONNMETRICS.DETAILS) 
      as "detmetric" COLUMNS "CLIENT_HOSTNAME" VARCHAR(255) PATH 'client_hostname' ) AS DETMETRICS; 
  FOR VERSION AS SELECT SCHEMA, PATH FROM APPSERVER AS S JOIN APPVERSION AS V ON S.VERSION = V.VERSION 
                  WHERE CLIENT_HOSTNAME = S.SERVERNAME 
  DO 
    SET STMTTXT = 'SET SCHEMA = ' || VERSION.SCHEMA; 
    EXECUTE IMMEDIATE STMTTXT; 
    SET STMTTXT = 'SET PATH = ' || VERSION.PATH; 
    EXECUTE IMMEDIATE STMTTXT; 
  END FOR; 
END; 
/ 
SET ENVVARSUBSTITUTION ON; 

UPDATE DB CFG USING CONNECT_PROC CONFIG.CONNECT_PROC; 

ALTER MODULE VERSION ADD FUNCTION LIST_EXTERNAL_DEPENDENCIES() RETURNS DEPENDENCY_LIST 
BEGIN 
  DECLARE I INTEGER DEFAULT 1; 
  DECLARE DEPENDENCY_LIST DEPENDENCY_LIST; 
  FOR dep AS SELECT DISTINCT * 
               FROM ( SELECT FROM_SCHEMA, FROM_MODULE, 
                             DECODE(FROM_TYPE, 'F', (SELECT ROUTINENAME FROM SYSCAT.ROUTINES 
                                                      WHERE FROM_SCHEMA = ROUTINESCHEMA 
                                                        AND (FROM_MODULE = ROUTINEMODULENAME OR (FROM_MODULE IS NULL AND ROUTINEMODULENAME IS NULL)) 
                                                        AND FROM_OBJECT = SPECIFICNAME), FROM_OBJECT) AS FROM_OBJECT, 
                             DECODE(FROM_TYPE, 'A', 'TABLE ALIAS', 'B', 'TRIGGER', 'F', 'ROUTINE', 'G', 'TABLE', 'H', 'TABLE', 'K', 'PACKAGE', 
                                               'L', 'TABLE', 'N', 'NICKNAME', 'O', 'TABLE', 'Q', 'SEQUENCE', 'R', 'TYPE', 'S', 'TABLE', 'T', 'TABLE', 
                                               'U', 'TABLE', 'V', 'VIEW', 'W', 'VIEW', 'X', 'INDEX EXT', 'Z', 'XSR OBJECT', 'm', 'MODULE', 
                                               'q', 'SEQUENCE ALIAS', 'u', 'MODULE ALIAS', 'v', 'VARIABLE', '*', 'TABLE', 'UNKNOWN: ' || FROM_TYPE) AS FROM_TYPE,
                             TO_SCHEMA, TO_MODULE, 
                             DECODE(TO_TYPE, 'F', (SELECT ROUTINENAME FROM SYSCAT.ROUTINES 
                                                    WHERE TO_SCHEMA = ROUTINESCHEMA 
                                                      AND (TO_MODULE = ROUTINEMODULENAME OR (TO_MODULE IS NULL AND ROUTINEMODULENAME IS NULL)) 
                                                      AND TO_OBJECT = SPECIFICNAME), TO_OBJECT) AS TO_OBJECT, 
                             DECODE(TO_TYPE, 'A', 'TABLE ALIAS', 'B', 'TRIGGER', 'F', 'ROUTINE', 'G', 'TABLE', 'H', 'TABLE', 'K', 'PACKAGE', 
                                             'L', 'TABLE', 'N', 'NICKNAME', 'O', 'TABLE', 'Q', 'SEQUENCE', 'R', 'TYPE', 'S', 'TABLE', 'T', 'TABLE', 
                                             'U', 'TABLE', 'V', 'VIEW', 'W', 'VIEW', 'X', 'INDEX EXT', 'Z', 'XSR OBJECT', 'm', 'MODULE', 
                                             'q', 'SEQUENCE ALIAS', 'u', 'MODULE ALIAS', 'v', 'VARIABLE', '*', 'TABLE', 'UNKNOWN: ' || TO_TYPE) AS TO_TYPE 
                        FROM (SELECT TYPESCHEMA AS FROM_SCHEMA, 
                                     TYPEMODULENAME AS FROM_MODULE, 
                                     TYPENAME AS FROM_OBJECT, 
                                     'R' AS FROM_TYPE, 
                                     BSCHEMA AS TO_SCHEMA, 
                                     BMODULENAME AS TO_MODULE, 
                                     BNAME AS TO_OBJECT, 
                                     BTYPE AS TO_TYPE 
                                FROM SYSCAT.DATATYPEDEP 
                              UNION ALL 
                              SELECT TABSCHEMA, 
                                     NULL, 
                                     TABNAME, 
                                     'T', 
                                     TYPESCHEMA, 
                                     NULL, 
                                     TYPENAME, 
                                     'R' 
                                FROM SYSCAT.COLUMNS 
                              UNION ALL 
                              SELECT TABSCHEMA, 
                                     NULL, 
                                     TABNAME, 
                                     'T', 
                                     BSCHEMA,
                                     BMODULENAME,
                                     BNAME, 
                                     BTYPE 
                                FROM SYSCAT.CONSTDEP 
                              UNION ALL 
                              SELECT MODULESCHEMA, 
                                     MODULENAME, 
                                     NULL,
                                     'u',
                                     BASE_MODULESCHEMA, 
                                     BASE_MODULENAME,
                                     NULL,
                                     'm'
                                FROM SYSCAT.MODULES 
                               WHERE MODULETYPE = 'A' 
                              UNION ALL 
                              SELECT P.PKGSCHEMA, 
                                     NULL, 
                                     P.PKGNAME, 
                                     'K', 
                                     P.BSCHEMA, 
                                     P.BMODULENAME, 
                                     P.BNAME, 
                                     P.BTYPE 
                                FROM SYSCAT.PACKAGEDEP AS P 
                                LEFT OUTER JOIN SYSCAT.ROUTINEDEP AS RD 
                                  ON P.PKGSCHEMA = RD.BSCHEMA AND P.PKGNAME = RD.BNAME AND RD.BTYPE = 'K' 
                                LEFT OUTER JOIN SYSCAT.ROUTINES AS R 
                                  ON RD.ROUTINESCHEMA = R.ROUTINESCHEMA 
                                  AND (RD.ROUTINEMODULENAME = R.ROUTINEMODULENAME OR (RD.ROUTINEMODULENAME IS NULL AND R.ROUTINEMODULENAME IS NULL)) 
                                  AND RD.SPECIFICNAME = R.SPECIFICNAME 
                               WHERE R.ORIGIN <> 'Q' OR R.ORIGIN IS NULL 
                              UNION ALL 
                              SELECT ROUTINESCHEMA, 
                                     ROUTINEMODULENAME, 
                                     SPECIFICNAME, 
                                     'F', 
                                     BSCHEMA, 
                                     BMODULENAME, 
                                     BNAME,
                                     BTYPE
                                FROM SYSCAT.ROUTINEDEP 
                                WHERE BTYPE <> 'K' 
                              UNION ALL 
                              SELECT ROUTINESCHEMA, 
                                     ROUTINEMODULENAME, 
                                     SPECIFICNAME, 
                                     'F', 
                                     TYPESCHEMA, 
                                     TYPEMODULENAME, 
                                     TYPENAME, 
                                     'R' 
                                FROM SYSCAT.ROUTINEPARMS 
                              UNION ALL 
                              SELECT TYPESCHEMA, 
                                     TYPEMODULENAME, 
                                     TYPENAME, 
                                     'R', 
                                     FIELDTYPESCHEMA, 
                                     FIELDTYPEMODULENAME,  
                                     FIELDTYPENAME, 
                                     'R' 
                                FROM SYSCAT.ROWFIELDS 
                              UNION ALL 
                              SELECT SEQSCHEMA, 
                                     NULL, 
                                     SEQNAME, 
                                     'q', 
                                     BASE_SEQSCHEMA, 
                                     NULL, 
                                     BASE_SEQNAME, 
                                     'Q' 
                                FROM SYSCAT.SEQUENCES 
                                WHERE SEQTYPE = 'A' 
                              UNION ALL 
                              (SELECT SEQSCHEMA, 
                                      NULL, 
                                      SEQNAME, 
                                      'Q', 
                                      TYPESCHEMA, 
                                      TYPEMODULENAME, 
                                      TYPENAME, 
                                      'R' 
                                 FROM SYSCAT.SEQUENCES AS S 
                                 JOIN SYSCAT.DATATYPES AS T 
                                   ON T.TYPEID = S.DATATYPEID) 
                              UNION ALL 
                              SELECT TABSCHEMA, 
                                     NULL, 
                                     TABNAME, 
                                     'T', 
                                     BSCHEMA, 
                                     BMODULENAME, 
                                     BNAME, 
                                     BTYPE
                                FROM SYSCAT.TABDEP 
                              UNION ALL 
                              SELECT TRIGSCHEMA, 
                                     NULL, 
                                     TRIGNAME, 
                                     'B', 
                                     BSCHEMA, 
                                     BMODULENAME, 
                                     BNAME, 
                                     BTYPE 
                                FROM SYSCAT.TRIGDEP 
                              UNION ALL 
                              SELECT TRIGSCHEMA, 
                                     NULL, 
                                     TRIGNAME, 
                                     'B',
                                     TABSCHEMA, 
                                     NULL,
                                     TABNAME, 
                                     'T' 
                                FROM SYSCAT.TRIGGERS 
                              UNION ALL 
                              SELECT VARSCHEMA, 
                                     VARMODULENAME, 
                                     VARNAME, 
                                     'v', 
                                     BSCHEMA, 
                                     BMODULENAME, 
                                     BNAME, 
                                     BTYPE 
                                FROM SYSCAT.VARIABLEDEP 
                              UNION ALL 
                              SELECT VARSCHEMA, 
                                     VARMODULENAME, 
                                     VARNAME, 
                                     'v', 
                                     TYPESCHEMA, 
                                     TYPEMODULENAME, 
                                     TYPENAME, 
                                     'R' 
                                FROM SYSCAT.VARIABLES 
                              UNION ALL 
                              SELECT OBJECTSCHEMA,  
                                     NULL, 
                                     OBJECTNAME, 
                                     'Z', 
                                     BSCHEMA, 
                                     BMODULENAME, 
                                     BNAME, 
                                     BTYPE 
                                FROM SYSCAT.XSROBJECTDEP) AS DEP, 
                             UNNEST(SOURCE) AS S(SCHEMA), 
                             UNNEST(EXCEPT) AS E(SCHEMA) 
                         WHERE FROM_SCHEMA = S.SCHEMA 
                           AND TO_SCHEMA <> E.SCHEMA AND TO_SCHEMA <> S.SCHEMA AND TO_SCHEMA NOT LIKE 'SYS%') 
                     ORDER BY FROM_SCHEMA, FROM_MODULE, FROM_TYPE, FROM_OBJECT, TO_SCHEMA, TO_MODULE, TO_TYPE, TO_OBJECT 
  DO 
    SET DEPENDENCY_LIST[I] = (FROM_SCHEMA, FROM_MODULE, FROM_OBJECT, FROM_TYPE, TO_SCHEMA, TO_MODULE, TO_OBJECT, TO_TYPE); 
    SET I = I + 1; 
  END FOR; 
  RETURN DEPENDENCY_LIST; 
END; 
/