How does one extract all stored procedures from a DB2 schema as individual files (without using Data Studio so it can be scripted). I need them all so I can uploaded to source control. I have found a way to do it via command line if one has access to the server, but do not have server access.
you can use the
db2look clp command with the
-e option, altough it does not have an option to only export procedures, so unless they are in a schema without other objects you will also get e.g. TABLE, VIEW and other DDL mixed in the output.
$ db2look -d BP -e -cor -nofed -z PAUL | grep -A 999 'DDL Statements for Stored Procedures' -- DDL Statements for Stored Procedures --------------------------------- SET SYSIBM.NLS_STRING_UNITS = 'SYSTEM'; SET CURRENT SCHEMA = "PAUL "; SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","PAUL"; CREATE OR REPLACE PROCEDURE P () BEGIN RETURN /* this */ 2 ; -- comment /* this is a comment */ END ; ------------------------------- -- DDL Statements for Triggers ------------------------------- SET SYSIBM.NLS_STRING_UNITS = 'SYSTEM'; ...
Exporting the TEXT column from
SYSCAT.PROCEDURES is another method.
As that column is a CLOB, you might want to use the clp feature that allows export of values longer than 32K
-- -- Simple method to overcome the 32K column limit on CLP output -- -- The example below will export the TEXT columns of the SYSCAT.VIEWS for a single row into a text file in the current directory called syscat.views.txt.001 -- -- This capability of the clp is documented here https://docs-db2.mybluemix.net/docs/content/SSEPGG_11.5.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0002135.html -- -- run from the Db2 CLP as follows --- -- db2 +c -td@ -f export_clobs.db2 DECLARE C0 CURSOR FOR SELECT TEXT FROM SYSCAT.VIEWS WHERE VIEWSCHEMA = 'SYSCAT' AND VIEWNAME = 'TABLES' WITH UR @ OPEN C0 @ FETCH FROM C0 LOB COLUMN ALL INTO syscat.tables.txt OVERWRITE @ CLOSE C0 @ COMMIT @
BTW you might also want to export the CURRENT PATH, CURRENT SCHEMA and NLS_STRING_UNITS if these were not the defaults when each of your SPs was created.
Use your favourite scripting language, loop over the procedures and write to file. Example in python:
#!/usr/bin/python import sys import ibm_db import getopt import os db = None host = "localhost" port = "50000" user = None pwd = None try: opts, args = getopt.getopt(sys.argv[1:], "h:d:P:u:p:") except getopt.GetoptError: sys.exit(-1) for o, a in opts: if o == "-d": db = a if o == "-h": host = a if o == "-P": port = a if o == "-u": user = a if o == "-p": pwd = a cfg = (db, host, port, user, pwd) conn = ibm_db.connect("DATABASE=%s; HOSTNAME=%s; PORT=%s; PROTOCOL=TCPIP; UID=%s; PWD=%s" % cfg, "", "") # SQL bodied routines read_routines = """select rtrim(specificname), rtrim(routineschema), rtrim(routinename), text, routinetype, remarks from syscat.routines where routineschema not like 'SYS%' and routineschema not like 'SQL%' and routinetype = 'P' and origin = 'Q' """ stmt = ibm_db.prepare(conn, read_routines) ibm_db.execute(stmt, ()) tpl = ibm_db.fetch_tuple(stmt) while tpl: specificname, routineschema, routinename, text, rtype, remarks = tpl text = os.linesep.join([s for s in text.splitlines() if s]) with open("%s.%s.sql" % (routineschema,routinename), "w") as f: f.write(text) f.write(" @") # statement terminator tpl = ibm_db.fetch_tuple(stmt)
In case you are interested, I have a parser that dumps out database objects (not only procedures) in the topological sort order so that you can recreate them in another database. It writes the objects to the same file, but you should be able to fix that quite easy
The parser is by no means complete, but it handles all objects that I needed at that time. It should be fairly easy to extend to other objects if needed