Introduction

With Version 7, DB2® Universal Database^TM 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.

Back to top

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

Back to top

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.

Back to top

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:

   put_line_aix.db2v72.tar.Z	for AIX
   put_line_aix.db2v81.tar.Z	for AIX
   put_line_hpux.db2v72.tar.Z	for HP-UX
   put_line_solaris.db2v72tar.Z	for Solaris
   put_line_linux.db2v72.tar.Z	for 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.

Back to top

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.

Back to top

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

  
  

Back to top

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     ) 
$ 

Back to top

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

Back to top

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.

Back to top

Download

Information about download methods

About the author

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• Prerequisites and tested environment
• Installing on Windows 2000
• Installing on UNIX and Linux
• Using the UDF
• Modifying and rebuilding the UDF
• Writing to a file
• Specifying the directory
• Conclusion
• Download
• About the author
• Comments

Local resources

• IBM Innovation Center - Austin, TX
• IBM Innovation Center - Chicago, IL
• IBM Innovation Center - Dallas, TX
• IBM Innovation Center - San Mateo, CA
• IBM Innovation Center - Waltham, MA
• Technical Briefings in North America

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers