A UDF for File Output and Debugging from SQL

This article describes a user-defined function that may be used for enabling direct file output from SQL. This capability is especially useful in debugging stored procedures. Sample code is included.

Share:

Sotaro Izuha, Consulting IT Specialist , IBM Japan

Sotaro Izuha is a Consuting IT Specialist in Software Group IBM Japan, responsible for country-wide technical sales support of DB2 UDB. He is a DB2 Certified Advanced Technical Expert (DRDA, Cluster/EEE and Replication) and a DB2 Certified Solutions Expert (Database Administration, Application Development and Business Intelligence). You can reach him at ikalos@jp.ibm.com.



25 February 2003

Also available in Japanese

Introduction

With Version 7, DB2® Universal DatabaseTM for Windows®, UNIX® and Linux introduced SQL Procedure Language. SQL Procedure Language allows SQL to be used to directly write procedures in a way similar to using traditional programming languages. With SQL Procedure Language, you can develop stored procedures, functions, triggers, and dynamic compound statements

The SQL Procedure Language has given programmers a new, easy tool for SQL development. However, like any new tool, it has its limitations. I have frequently been asked if there is a way to write to a file from an SQL stored procedure. The answer is NO. You cannot write directly to a file using SQL procedure statements alone.

To help programmers handle this requirement, I have developed a user-defined function (UDF) solution called PUT_LINE that will enable file output from pure SQL, such as an SQL stored procedure. Originally, I wrote it as a tool for debugging stored procedures under Windows 2000, but it also allows you to write to a specified file for other purposes as well. You can run it not only on Windows 2000, but also on IBM AIX®, HP-UX, Sun Solaris and Linux.


Prerequisites and tested environment

The PUT_LINE UDF requires DB2 for Windows, UNIX or Linux V7.2 or V8.1.

PUT_LINE was tested in a single partition database on the following platforms:

  • Windows 2000 SP2: DB2 V7.2 FP7, V8.1
  • IBM AIX 5.1: DB2 V7.2, V8.1
  • HP-UX 11.0: DB2 V7.2
  • Solaris 8: DB2 V7.2
  • Linux: DB2 V7.2

Installing on Windows 2000

Follow these steps to prepare to use PUT_LINE on Windows:

  1. Download put_line.zip, unzip the Windows version to an appropriate directory, and change to that directory.
  2. Open a DB2 command window and establish the database connection:
    db2 connect to sample
  3. Execute CREATE FUNCTION using the crefunc.sql file which is included with PUT_LINE:
    db2 -tvf crefunc.sql
  4. Copy the UDF to the db2 function directory:
    copy put_line.dll "%DB2PATH%\function\."

Now the UDF is available.


Installing on UNIX and Linux

Follow these steps to prepare to use PUT_LINE on UNIX and Linux:

  1. Unzip the platform-specific file from put_line.zip.
    The files are:
    FileOperating system
    put_line_aix.db2v72.tar.Zfor AIX
    put_line_aix.db2v81.tar.Zfor AIX
    put_line_hpux.db2v72.tar.Zfor HP-UX
    put_line_solaris.db2v72tar.Zfor Solaris
    put_line_linux.db2v72.tar.Zfor Linux
  2. Ftp the appropriate one of the above files to the UNIX or Linux machine as binary as the db2 instance owner.
  3. Telnet to the machine as the db2 instance user, uncomppress and issue the command tar -xvf, for example:
    uncompress put_line_linux.db2v72.tar.Z 
    tar -xvf put_line_linux.db2v72.tar
  4. Establish the database connection:
    db2 connect to sample
  5. Execute CREATE FUNCTION using the crefunc.sql file which is included with PUT_LINE:
    db2 -tvf crefunc.sql
  6. Copy the UDF to the db2 function directory:
    cp  put_line $HOME/sqllib/function/.
  7. Unzip the platform-specific file from put_line.zip, ftp to the UNIX machine as binary, uncompress and issue the command tar -xvf it.
  8. Create the function and copy the module under sqllib/function.

Using the UDF

This UDF supports three formats for writing out debugging information.

  • To write a message, first you must declare a variable for the VALUES statement:
    DECLARE dummy_variable VARCHAR(1);

    Then PUT_LINE can be used as follows:

    VALUES (PUT_LINE('debug message')) INTO dummy_variable;
  • To write a number, the PUT_LINE function is used as follows:
    VALUES (PUT_LINE(dubug number) INTO dummy_variable;
  • To write a number and a message, use PUT_LINE as follows:
    VALUES (PUT_LINE(dubug number),'dubug message'))  
      INTO dummy_variable;

Parameter specification

You can specify either a number or text or both.

debug number:

A number between 0 and 32767. It can be declared as SMALLINT or INTEGER.

debug message:

A message text or variable varchar(4000).

dummy_variable:

Any variable name declared as varchar(1).

Here is the syntax:

  • Writing a debug number:
    PUT_LINE(SMALLINT) 
    PUT_LINE(INTEGER)
  • Writing text:
    PUT_LINE(VARCHAR(4000))
  • Writing a number and text:
    PUT_LINE(SMALLINT,VARCHAR(4000)) 
    PUT_LINE(INTEGER,VARCHAR(4000))

Output file name

The UDF generates the output file name for you. It will be generates as follows:

%DB2PATH%\%DB2INSTANCE%\sqlproc_debug_file.ProcessId.TXT    for Windows 
/tmp/sqlproc_debug_file.PROCESS_ID.TXT			for UNIX/Linux

Here, ProcessId is the ID of the process in which the UDF is executed. When you create the function using the NOT FENCED option that is specified in this sample, it is the process id of db2sysc in Windows or db2agent in UNIX. It is a variable assigned by the operating system when you start DB2, and therefore the file name is not fixed. This prevents the file from continually growing in size as long as you restart DB2 once in a while. For example, if you start your DB2 every morning and shutdown every evening, there will be one file for each day.

Note that in the Windows environment, if multiple users invoke this UDF, the output will be routed to the same file. Therefore I recommend that you use it in a personal development environment, one user in an instance. In v7 and with the FENCED option of the UDF, the files are separately created for each connection. However, in v8, due to the UDF implementation change, the FENCED option does not assign a separate process id.

Sample usage for debugging

I have included with my sample code an installation verification procedure called testcase.sqp. It will dump the ORG table in the SAMPLE database, while showing which step is executed by the number or text you specified.

Listing 1. testcase.sqp

CREATE PROCEDURE TESTCASE() RESULT SETS 0 LANGUAGE SQL 
L_TESTCASE: 
BEGIN NOT ATOMIC  
	DECLARE V_DEPTNO	SMALLINT; 
	DECLARE V_DEPTNAME	VARCHAR(20); 
	DECLARE V_DIVISION	VARCHAR(20); 
-- FOR DEBUG 
	DECLARE V_NUM	SMALLINT DEFAULT 0; 
	DECLARE V_MSG1	VARCHAR(4000); 
	DECLARE	V_MSG2	VARCHAR(1); 
-- END 
-- SAMPLE1 
	SET V_NUM=2000; 
	SET V_MSG1='debugging start'; 
	VALUES(PUT_LINE(V_NUM,V_MSG1)) INTO V_MSG2; 
-- SAMPLE2 
	VALUES(PUT_LINE(SMALLINT(2),'this is no2'))  
	  INTO V_MSG2; 
-- SAMPLE3 
	VALUES(PUT_LINE(3)) INTO V_MSG2; 
-- SAMPLE4 
	VALUES(PUT_LINE('##number4##')) INTO V_MSG2; 
-- SAMPLE5 
	SET V_NUM = 0; 
   FOR V_C1_REC AS C1 CURSOR FOR 
	SELECT DEPTNUMB, DEPTNAME, DIVISION 
	FROM ORG ORDER BY DEPTNUMB DESC 
   DO 
      	SET V_DEPTNO   = v_C1_REC.DEPTNUMB; 
	SET V_DEPTNAME = v_C1_REC.DEPTNAME; 
 	SET V_DIVISION = v_C1_REC.DIVISION; 
	SET V_NUM = V_NUM + 1; 
	SET V_MSG1 ='DEPTNO=' || CHAR(V_DEPTNO)||',' 
		||'DEPTNAME='||V_DEPTNAME||',' 
		||'DIVISION='||V_DIVISION; 
	VALUES(PUT_LINE(V_NUM,V_MSG1)) INTO V_MSG2; 
   END FOR; 
	VALUES(PUT_LINE(32000,'end of the program'))  
	  INTO V_MSG2; 
	   
END	L_TESTCASE 
@

In order to register it in the database and run it, follow these steps:

  1. Create the verification SQL procedure:
    db2 -td@ -vf testcase.sqp
  2. Execute the verification SQL procedure:
    db2 call testcase()		for Windows 
    db2 "call testcase()"	for UNIX/Linux
  3. To view the file where the messages were written:
    TYPE "%DB2PATH%\%DB2INSTANCE%\SQLPROC_DEBUG_FILE.*"	  for Windows 
    cat /tmp/sqlproc_debug_file.*				for UNIX/Linux

Listing 2. Messages

Wed Oct 30 15:35:53.275 ( 2000) debugging start 
Wed Oct 30 15:35:53.275 (    2) this is no2 
Wed Oct 30 15:35:53.345 (    3) 
Wed Oct 30 15:35:53.345 ##number4## 
Wed Oct 30 15:35:53.395 (    1) DEPTNO=84  ,DEPTNAME=Mountain 
Wed Oct 30 15:35:53.395 (    2) DEPTNO=66  ,DEPTNAME=Pacific 
Wed Oct 30 15:35:53.405 (    3) DEPTNO=51  ,DEPTNAME=Plains 
Wed Oct 30 15:35:53.405 (    4) DEPTNO=42  ,DEPTNAME=Great Lakes 
Wed Oct 30 15:35:53.415 (    5) DEPTNO=38  ,DEPTNAME=South Atlantic 
Wed Oct 30 15:35:53.415 (    6) DEPTNO=20  ,DEPTNAME=Mid Atlantic 
Wed Oct 30 15:35:53.415 (    7) DEPTNO=15  ,DEPTNAME=New England,DIVISION=Eastern 
Wed Oct 30 15:35:53.425 (    8) DEPTNO=10  ,DEPTNAME=Head Office,DIVISION=Corporat 
Wed Oct 30 15:35:53.425 (32000) end of the program

As you can see, each line is prefixed by the date and time when it is written so you can find what you are looking for.


Modifying and rebuilding the UDF

The source program is put_line.c. If you modify the file and want to recompile and rebuild it, issue following command for DB2 v7 Windows:

"%DB2PATH%\SAMPLES\C\BLDMUDF.BAT" PUT_LINE

If you rebuild the function for UNIX, use the provided sample script, adding the operating-system specific define, -Dxxxx as follows. Ensure the copy step in the script is executed successfully.

  • AIX DB2 v8:
    Add -Daix in copy of sqllib/samples/c/bldrtn.
    X="put_line" 
    /usr/vac/bin/xlc_r -I$HOME/sqllib/include -qmkshrobj $X.c  
       -o $X -L$HOME/sqllib/lib -ldb2 -bE:$X.exp -Daix 
    rm -f $HOME/sqllib/function/$X 
    cp $X $HOME/sqllib/function/.
  • AIX DB2 v7.2:
    Add -Daix in cc parameter in copy of bldudf of sqllib/samples/c.
    xlc $CFLAGS_64 -I$DB2PATH/include -c $1.c -Daix
  • HP-UX DB2 v7.2:
    Add -Dhpux in cc parameter in copy of bldudf of sqllib/samples/c.
    cc +DAportable +u1 +z -Aa +e -I$DB2PATH/include -c $1.c -Dhpux
  • Solaris 8 DB2 v7.2:
    Add -Dsolaris in cc parameter in copy of bldudf of sqllib/samples/c.
    cc $CFLAGS_64 -Kpic -I$DB2PATH/include -c $1.c -Dsolaris
  • Linux DB2 v7.2:
    Add -Dlinux in gcc parameter in copy of bldudf of sqllib/samples/c.
    gcc -I$DB2PATH/include -c $1.c -Dlinux

Writing to a file

In the above example, the file name is automatically generated by the UDF. At times you may have application requirements for writing to a file rather than DB2 tables. In such cases, you may want to specify a filename for output. This UDF allows you to write up to 4000 bytes texts or varchar rows to the file name you specified. You can specify the directory of the file by an environment variable PUT_LINE_OUT. If you don't, it will use /tmp for UNIX, or %DB2PATH%\%DB2INSTANCE% for Windows.

Parameter specification for file output

PUT_LINE(filename, filemode, text)

filename: file name for output VARCHAR(4000)
filemode: file open mode 'w' for write or 'a' for append VARCHAR(1)
text: text to write VARCHAR(4000)

Example for file output

I have included two sample DB2 Command Line Processor files, testcase.sql for Windows and testcase2_unix.sql for UNIX. As a simple example, the SELECT statements write or append all the schema names used in the database to the file.

Listing 3. Two normal samples and two error samples

-- file name, write mode, column name 
select schemaname, put_line('XYZ.DAT','w',schemaname)  
from syscat.schemata; 
-- file name, append mode, column name 
select schemaname, put_line('XYZ.DAT','a','('||schemaname||')')  
from syscat.schemata; 
 
-- ERROR SAMPLES 
-- file name, write mode, column name 
select schemaname, put_line('..\XYZ.DAT','w',schemaname)  
  from syscat.schemata; 
-- file name, append mode, column name 
select schemaname, put_line('..\XYZ.DAT','a','('||schemaname||')')  
from syscat.schemata; 
-- file open error due to invalid path name

The first parameter is the file name you want to write to and the second parameter is the file open mode, for example, "w" for write replace, and "a" for write append.

If successful, '1' is returned, if not, SQL NULL is returned for file open error, and '0' for any other errors from the UDF.

The above error examples demonstrate that the UDF does not allow the file beyond the directory where the files are to be created. To accomplish this, the UDF checks the existence of '..' string. If found it returns '0' without writing anything.

Sample output file

When the directory specification is default, the above first two SELECT statements write to the file /tmp/XYZ.DAT in UNIX. You can see the contents by typing cat /tmp/XYZ as illustrated below.

$ cat /tmp/XYZ.DAT 
SYSIBM 
SYSCAT 
SYSFUN 
SYSSTAT 
NULLID 
DB2INST1 
ASN 
(SYSIBM  ) 
(SYSCAT  ) 
(SYSFUN  ) 
(SYSSTAT ) 
(NULLID  ) 
(DB2INST1) 
(ASN     ) 
$

Specifying the directory

You can override the default directory, /tmp for UNIX, or %DB2PATH%\%DB2INSTANCE% for Windows, as follows:

On Windows

Click Start -> Settings -> Control Panel -> System -> Advanced ->Environment Variables
Set New System variable PUT_LINE_OUT and set the value as the directory name.
db2stop / db2start

On Unix

Enter the following commands:
export PUT_LINE_OUT=the directory name
db2set DB2ENVLIST=PUT_LINE_OUT
db2start
For V8, also add the following lines to sqllib/db2profile:
PUT_LINE_OUT=the directory name
export PUT_LINE_OUT


Conclusion

File I/O capability is one of the commonly used functions provided by operating systems, but out of scope of standard SQL. However, you can enhance your SQL capabilities with this user-defined function for both debugging and application output purposes. I hope you try the PUT_LINE UDF to increase your own productivity with SQL.


Download

DescriptionNameSize
Code sampleput_line.zip108 KB

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=14202
ArticleTitle=A UDF for File Output and Debugging from SQL
publish-date=02252003