1

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.

2
  • You don't need "server access" (apart from the ability to connect to it) to run the Db2 CLP; not sure what problem you have running your command line, provided you have installed the Db2 client locally.
    – mustaccio
    Jan 14 at 14:19
  • I am not sure what is installed on my PC other than Data Studio, DBeaver, CPLpluss. All attempts to write files have always appeared on the server. Using CLPplus worked using spool, but there seems to be a bug setting LINESIZE.
    – Jake v1
    Jan 15 at 15:13
1

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';
...




    
5
  • Thanks Paul, This is a great command to know. I had a co-worker who has access to the server run it and it gets everything I need. But two problems... I don't have access to the server and it does not generate individual files.
    – Jake v1
    Jan 14 at 15:01
  • You can run db2look remotely, but it is only included in the Db2 Server install... so (officially) you need to have a local db2 server install (that ideally matches the same database version as the server), then you can run db2look on a remotely cataloged server Jan 14 at 15:09
  • try my other answer if you want one file per SP. Just run it for each SP you want to export. Jan 14 at 15:13
  • Thanks again Paul. I will end up using one of these two options. It is not ideal for me. As a developer I did not want to have access to the database directly and was hoping to be able to use CLPplus or Data Studio to create scripts to do this. All things point to having the files generated on the server file system which is unfortunate. I have an Oracle solution using SQLplus and spool to write the files, but there seems to be a bug in CLPplus setting the max line size so everything gets truncated.
    – Jake v1
    Jan 14 at 20:16
  • The clp FETCH thing will work remotely, you just need to have the IBM Data Server Runtime Client rather than just the IBM Data Server Driver Package to get the clp Jan 14 at 23:43
0

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.

1
  • Note that the above only supports outputting a single row. EXPORT with the LOBS option might be a better solution to export stored proc TEXT that are longer than 32 or 64K bytes (and if you don;t have e.g. db2look Jan 14 at 15:11
0

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

DBParser

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.