Online Rolling Application Upgrades

MotivationImagine you are running a website likeonline banking. Imagine further you want to get as closeto 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 hardwareand OS upgrades.  What none of them do is helping youdeploy application upgrades!Traditionally when you upgrade anapplication you will take a planned outage. Then you tear down the existing application objects on the database and thenrebuild the new version.This can take some time. A customer I am working with is howeverasking to do scheduled application upgrades once a quarter - with zeroimpact to their online presence.The lay of the landLet's first take a look at the topologyof such an app:1.    There is the client side applicationwhich is most likely written in Java2.    There are multiple application servers(Websphere or Weblogic most likely)3.    There is server side logic present ino    Moduleso    PL/SQL Packageso    Functionso    Procedureso    Typeso    VariablesThis application is ultimately runningon a database schema consisting of·       Tables·       Views·       Indexes·       Sequences·       Triggers·       .. and data :-)In a serious development environment theapplication source will be tightly controlled  in some version controlsystem with methods to deploy on a test system, integration system andproduction system. Upgrading the client side part of theapplication is rather straight forward. All it takes is a rolling upgrade through the webservers. One web server at a time is taken offline, upgraded and thenre-integrated.But how do we upgrade the server side application? DB2 does not supportexplicit versioning of modules and the likes.    Making it happenLuckily DB2 supports something elsewhich does the job very nicely as long as some basic best practices arefollowed. DB2 resolves application objects by PATH.In DB2 you can have objects with thesame name in multiple schemata. Using the session level PATH registry variable you can control whichapplication sees which objects.There is no hard limit on how many schematacan be used.  Unlike with other DBMS such as Oracle, there is no also security concernbecause 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 rulesfor our application:·       The source code of the objects must not use explicitqualifiers 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 ORREPLACE foo() ...Bad:  CREATE ORREPLACE myapp.foo()...·       When referencing the db schema objects a schema name mustbe used unless a public synonym existsGood:  SELECT *FROM myschema.t;Bad:  SELECT *FROM t;·       The schemata which are holding tables, views, sequencesmust be distinct from the application schema.Good:   Routine myapp.foo() and table myschema.tBad:   Routine myschema.foo() and table myschema.t ·       Avoid overloading procedures and functions by name acrossmultiple schemata, unless they are in modules or PL/SQL packagesGood:  Procedures hr.hire() and finance.income()Bad:  Procedures hr.add() and finance.add()With these rules we can deploy theapplication into any set of schemata simply by setting the CURRENTSCHEMA and CURRENT PATH registers beforeexecuting the DDL.The client application then uses thesame CURRENT PATH to find the application.From here it's just a small step and weare there.We just have to come up with a naming convention to version application schemanames and a means to manage which application server connects to which version.For naming we can trail the versionnumber 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 thelog-on procedure I introduced in my very first blog-post provides the perfectintercept point. This was a lot of theory. Time to builda real example.Example Of course we won't use a threetier architecture here. That would blow my modest set-up.While you can choose any number of schemata for your application we'll alsostay with one schema here. First we need to install a "versionmanagement 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 canexecute it from CLPPlus like this:@@vmsThe 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 controlsystem 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.sqlCALL version.register('App 1.0', 'DATA', CURRENT_PATH);CALL server.register('srielau', 'App 1.0');COMMIT;Run App 1.0 Time to fire upour application in a separate CLPPlus window.When we connect the connect procedure will matchthe host name with the application version.it then sets the PATH to include"APP_1_0" and the SCHEMA to "DATA". 点击(此处)折叠或打开BEGINDECLARE id ANCHOR TO data.emp.id;LOOPCALL hire_emp(id, 'John', 20000);COMMIT;CALL fire_emp('John');COMMIT;END LOOP;END;/While our application is being exercisedwe want to do an upgrade. Firing employees by name seems like a recipe for disaster in case ofduplicates. Instead we should use employee ids since they are primary keys.Our server side application logic willnow 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.sqlCALL version.register('App 1.1', 'DATA', CURRENT PATH);COMMIT;To ensure that the new applicationversion doesn't "cross talk" I have provided a small functionthat 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----------- 0Production grade rolling upgradeNormally you now perform the followingsteps:1.    Drain an application server bydisallowing new client connections2.    Update the application server3.    Update the application version for theapplication server's host name using the SERVER.UPDATE procedure4.    re-integrate the application server. "Laptop grade" rollingupgrade Since I'm doing this experiment onmy laptop I only have one application server.So I will update the version while my one CLPPlus shell is still executing App1.0.Then I fire up a new CLPPlus shell with a new connection to demonstrate bothapplication 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 CLPPlusapplication I need to run the new client application of course.The snapshot below shows:1.    Top-Left the first connection runningApp.1.02.    Top-Right the second connection runningApp 1.13.    Bottom left the console used for alladministration4.    Both apps are firing away concurrentlysaturating the laptop.Rolling upgrade rulesWhat is important to note here is that:·       We deploy a completely new version of the applicationeven though we just changed one of the two procedures. ·       The deployment was 100% online. It did not affect therunning of the first version beyond CPU and IO resources to compile and deploy·       If you want to subdivide the application into multipleschemas that is fine, but keep in mind that the application of a given versionwill 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 youadd a new version HR_1_4 invoking FINANCE_3_1 even if nothing changed in HR.Both applications can work at the sametime here. But you must design for that.If one application, for example produces data which the other cannot ingestthen there will be failures.This implies that for a successful online "rolling upgrade" youmay need to use two steps:1.    One preparation step which merelyenhances the application to cope with unexpected data.2.    Once that preparation step is completelyrolled out the extra function can be added. But again the new application version has to be tolerant of the previousversion's effects on the database. Version Management System I have written a simple set ofroutines in a VERSION module which manage the association of different PATH andSCHEMA settings to application versions. A second set of routines picks up these versions and associates them with hostnames which represent application servers. A connection procedure ensures the settings are applied accordingly based on aconnection's host name. You can of course add additional properties to versions, such as isolationlevels or user defined variables. You may also want to replace HOSTNAME with another identifier for theapplication server.Lastly the code below supplies a routine which double checks that applicationsource code is "pure". The goal is that there is no cross talk across versions. Here is a quick overview:·       VERSION.REGISTER(VERSION VARCHAR(10), SCHEMAVARCHAR(128), PATH VARCHAR(2048)) This procedure defines a version and sets its properties. The schema willbe used by dynamic queries to resolve unqualified table, view and sequencenames. The PATH will be used to resolve any routines, modules, types andvariables. ·       VERSION.DEREGISTER(VERSION VARCHAR(10)) This procedure removes a version. The version must not be used by anyserver. Note that removal of the version does not drop any objects. ·       VERSION.UPDATE(VERSION VARCHAR(10), SCHEMAVARCHAR(128), PATH VARCHAR(2048)) This procedure updates an existing version to new properties. If SCHEMAis 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 associatedserver. ·       VERSION.SOURCE SCHEMA_LIST This variable needs to be set to an array of schemata which make up anapplication 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 byVERSION.LIST_EXTERNAL_DEPENDENCIES(). Typically this will be the list ofschemata 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 objectsreferenced by any object in a schema set in VERSION.SOURCE which is neitherlisted in VERSION.SOURCE nor VERSION.EXCEPT. That is the function lists anyreference to an object outside of the application version. It should return anempty 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), VERSIONVARCHAR(10)) This procedure registers an application server identified by the hostnameand 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), VERSIONVARCHAR(10)) This procedure updates an application server identified by the hostnameto a new application version. The next connection from this SERVERNAME will use the PATH and SCHEMA of theassociated VERSION. vms.sql1.  SET SCHEMA = CONFIG; SET PATH = SYSTEM PATH, CONFIG; BEGIN   DECLARECONTINUE HANDLER FOR SQLSTATE '42704' BEGIN END;   EXECUTEIMMEDIATE 'DROPTABLE APPSERVER';   EXECUTEIMMEDIATE 'DROPTABLE APPVERSION'; END; / CREATE TABLEAPPSERVER(SERVERNAME VARCHAR(32) NOT NULL PRIMARY KEY,                       VERSION    VARCHAR(10)NOT NULL); CREATE TABLEAPPVERSION(VERSION VARCHAR(10)   NOT NULL PRIMARY KEY,                        SCHEMA  VARCHAR(128)  NOT NULL,                        PATH    VARCHAR(2048) NOT NULL); ALTER TABLEAPPSERVER ADD CONSTRAINT APPVERSION FOREIGN KEY (VERSION)                          REFERENCES APPVERSION ON DELETE RESTRICT ON UPDATE RESTRICT; CREATE ORREPLACE MODULE VERSION; / CREATE OR REPLACEPUBLIC SYNONYM VERSION FOR MODULE VERSION;/ ALTER MODULEVERSION PUBLISH PROCEDURE REGISTER(VERSION ANCHOR TO APPVERSION.VERSION,                                                SCHEMA  ANCHOR TO APPVERSION.SCHEMA,                                                PATH    ANCHOR TO APPVERSION.PATH); / ALTER MODULEVERSION PUBLISH PROCEDURE DEREGISTER(VERSION ANCHOR TO APPVERSION.VERSION); / ALTER MODULEVERSION PUBLISH PROCEDURE UPDATE(VERSION ANCHOR TOAPPVERSION.VERSION,                                              SCHEMA  ANCHOR TO APPVERSION.SCHEMA DEFAULT (NULL),                                              PATH    ANCHOR TO APPVERSION.PATH   DEFAULT (NULL)); / ALTER MODULEVERSION PUBLISH TYPE SCHEMA_LIST AS VARCHAR(128) ARRAY[]; / ALTER MODULEVERSION 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 MODULEVERSION PUBLISH TYPE DEPENDENCY_LIST AS DEPENDENCY ARRAY[]; / ALTER MODULEVERSION PUBLISH VARIABLE SOURCE SCHEMA_LIST; / ALTER MODULEVERSION PUBLISH VARIABLE EXCEPT SCHEMA_LIST; / ALTER MODULEVERSION PUBLISH FUNCTION LIST_EXTERNAL_DEPENDENCIES() RETURNS DEPENDENCY_LIST; / CREATE ORREPLACE MODULE SERVER; / CREATE OR REPLACEPUBLIC SYNONYM SERVER FOR MODULE SERVER; / ALTER MODULESERVER PUBLISH PROCEDURE REGISTER(SERVERNAME ANCHOR TO APPSERVER.SERVERNAME,                                               VERSION    ANCHOR TO APPSERVER.VERSION); / ALTER MODULESERVER PUBLISH PROCEDURE DEREGISTER(SERVERNAME ANCHOR TO APPSERVER.SERVERNAME); / ALTER MODULESERVER PUBLISH PROCEDURE UPDATE(SERVERNAME ANCHORTO APPSERVER.SERVERNAME,                                              VERSION   ANCHOR TO APPSERVER.VERSION); / ALTER MODULEVERSION ADD PROCEDURE REGISTER(VERSION ANCHOR TOAPPVERSION.VERSION,                                            SCHEMA  ANCHOR TO APPVERSION.SCHEMA,                                            PATH    ANCHOR TO APPVERSION.PATH DEFAULT(NULL)) BEGIN   DECLAREEXIT HANDLER FOR SQLSTATE '23505'   BEGIN    DECLARE txt VARCHAR(70);    SET txt = 'Version: ''' || VERSION || ' already exists.';    SIGNAL SQLSTATE '78000' SETMESSAGE_TEXT = txt;   END;   INSERT INTOAPPVERSION VALUES(VERSION, SCHEMA, COALESCE(PATH, 'SYSTEM PATH, "' || SCHEMA || '"')); END; / ALTER MODULEVERSION ADD PROCEDURE DEREGISTER(VERSION ANCHOR TO APPVERSION.VERSION) BEGIN   DECLAREEXIT HANDLER FOR SQLSTATE '02000'   BEGIN    DECLARE txt VARCHAR(1000);    SET txt = 'Version: ''' || VERSION || ''' not found.';    SIGNAL SQLSTATE '78000' SET MESSAGE_TEXT = txt;   END;   DECLAREEXIT HANDLER FOR SQLSTATE '23001'   BEGIN    DECLARE txt VARCHAR(4000);    SELECT '''' || VERSION || ''' used by ' || '''' || LISTAGG(SERVERNAME) WITHINGROUP (ORDER BY SERVERNAME) || '''' INTO txt      FROM APPSERVER WHERE VERSION = DEREGISTER.VERSION GROUP BY VERSION;    SIGNAL SQLSTATE '78000' SET MESSAGE_TEXT = txt;   END;   DELETEFROM APPVERSION WHERE VERSION = DEREGISTER.VERSION; END; / ALTER MODULEVERSION ADD PROCEDURE UPDATE(VERSION ANCHOR TOAPPVERSION.VERSION,                                          SCHEMA  ANCHOR TO APPVERSION.SCHEMA DEFAULT (NULL),                                          PATH    ANCHOR TO APPVERSION.PATH    DEFAULT (NULL)) BEGIN  DECLAREEXIT HANDLER FOR SQLSTATE '02000'  BEGIN   DECLARE txt VARCHAR(70);    SETtxt = 'Version: ''' || VERSION || ''' not found.';   SIGNAL SQLSTATE '78000' SETMESSAGE_TEXT = txt;  END;  UPDATEAPPVERSION SET SCHEMA = COALESCE(UPDATE.SCHEMA, SCHEMA),                        PATH   = COALESCE(UPDATE.PATH, PATH)    WHEREVERSION = UPDATE.VERSION; END; / ALTER MODULESERVER ADD PROCEDURE REGISTER(SERVERNAME ANCHOR TO APPSERVER.SERVERNAME,                                           VERSION    ANCHOR TO APPSERVER.VERSION) BEGIN   DECLAREEXIT HANDLER FOR SQLSTATE '23505'   BEGIN    DECLARE txt VARCHAR(70);    SET txt = 'Server: ''' || SERVERNAME || ' already exists.';    SIGNAL SQLSTATE '78000' SET MESSAGE_TEXT = txt;   END;   DECLAREEXIT HANDLER FOR SQLSTATE '23503'   BEGIN   DECLARE txt VARCHAR(70);    SET txt = 'Version: ''' || VERSION || ''' does not exist.';    SIGNAL SQLSTATE '78000' SET MESSAGE_TEXT = txt;   END;   INSERTINTO APPSERVER VALUES(SERVERNAME, VERSION); END; / ALTER MODULESERVER ADD PROCEDURE DEREGISTER(SERVERNAME ANCHORTO APPSERVER.SERVERNAME) BEGIN   DECLAREEXIT HANDLER FOR SQLSTATE '02000'   BEGIN    DECLARE txt VARCHAR(70);    SET txt = 'Server: ''' || SERVERNAME || ''' not found.';    SIGNAL SQLSTATE '78000' SETMESSAGE_TEXT = txt;   END;   DELETE FROMAPPSERVER WHERE SERVERNAME = DEREGISTER.SERVERNAME; END; / ALTER MODULESERVER ADD PROCEDURE UPDATE(SERVERNAME ANCHOR TO APPSERVER.SERVERNAME,                                          VERSION   ANCHOR TO APPSERVER.VERSION) BEGIN   DECLAREEXIT HANDLER FOR SQLSTATE '02000'   BEGIN    DECLARE txt VARCHAR(70);    SET txt = 'Server: ''' || SERVERNAME || ''' not found.';    SIGNAL SQLSTATE '78000' SET MESSAGE_TEXT = txt;   END;   DECLAREEXIT HANDLER FOR SQLSTATE '23503'   BEGIN    DECLARE txt VARCHAR(70);    SET txt = 'Version: ''' || VERSION || ''' does not exist.';    SIGNAL SQLSTATE '78000' SET MESSAGE_TEXT = txt;   END;   UPDATEAPPSERVER SET VERSION = UPDATE.VERSION WHERE SERVERNAME = UPDATE.SERVERNAME; END; / SETENVVARSUBSTITUTION OFF; CREATE OR REPLACEPROCEDURE CONNECT_PROC() SPECIFICCONNECT_PROC BEGIN   DECLARESTMTTXT VARCHAR(4000);   DECLARECLIENT_HOSTNAME ANCHOR TO APPSERVER.SERVERNAME;   SELECTDETMETRICS.CLIENT_HOSTNAME INTO CLIENT_HOSTNAME    FROM TABLE(MON_GET_CONNECTION_DETAILS(MON_GET_APPLICATION_HANDLE(), -1)) ASCONNMETRICS,         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 VERSIONAS SELECT SCHEMA, PATH FROM APPSERVER AS S JOINAPPVERSION 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; / SETENVVARSUBSTITUTION ON; UPDATE DB CFGUSING CONNECT_PROC CONFIG.CONNECT_PROC; ALTER MODULEVERSION ADD FUNCTION LIST_EXTERNAL_DEPENDENCIES() RETURNS DEPENDENCY_LIST BEGIN   DECLARE IINTEGER DEFAULT 1;   DECLAREDEPENDENCY_LIST DEPENDENCY_LIST;   FOR depAS 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 ANDROUTINEMODULENAME 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 ROUTINEMODULENAMEIS 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,                                      TYPEMODULENAMEAS FROM_MODULE,                                      TYPENAMEAS FROM_OBJECT,                                      'R'AS FROM_TYPE,                                      BSCHEMAAS TO_SCHEMA,                                      BMODULENAMEAS TO_MODULE,                                      BNAMEAS TO_OBJECT,                                      BTYPEAS 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 ISNULL 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'                                  FROMSYSCAT.SEQUENCES AS S                                  JOINSYSCAT.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 ANDTO_SCHEMA NOT LIKE 'SYS%')                      ORDERBY 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;   RETURNDEPENDENCY_LIST; END; /