SQL compatibility in CLPPlus

A guide to a unified command-line tool for migrating and existing DB2 users

CLPPlus provides essential functionality for users migrating scripts from PL/SQL to IBM® DB2®, as well as many new features that enable DB2 and Informix® users to create formatted reports and perform common tasks quickly and efficiently. In this article, get started with CLPPlus and explore the support for PL/SQL dialects. Also learn how easy it is to format reports and set up scripts for common tasks. Finally, the article explains how to use AUTOTRACE with CLPPlus to get information for performance tuning, using the SQL Buffer Utility for developing large SQL blocks, and using spooling to assist in developing scripts.

Share:

Mangesh Shanbhag (manshanb@in.ibm.com), Staff Software Engineer, Common Application Development, IBM

Mangesh Shanbhag photoMangesh is a staff software engineer working with IBM India for past three and a half years. Since then he has been associated with Information Management tools development in India Software Lab. He has around five years of total experience in software product development. Currently he is working with the Common Application Development team, where he is developing CLPPlus reporting, scripting, and SQL compatibility features. He has a B.E. in computer Science from VTU Karnataka (India). In his free time, he loves to read books and watch movies. He is also an avid sports fan.



01 September 2011

Also available in Chinese Vietnamese

Overview

The command-line processor plus tool, CLPPlus, provides essential features for migrating to DB2 from Oracle and also for DB2 and Informix users. Using CLPPlus, users migrating to DB2 can run existing SQL scripts against DB2 for Linux®, UNIX®, and Windows® with little or no modification. Existing DB2 users can also take advantage of some of the new features and benefits of CLPPlus that enable DBAs to perform many common tasks quickly and efficiently. CLPPlus is a simple and helpful tool for users in creating formatted reports or in managing PL/SQL or SQL scripts in a familiar way. Also, its sophisticated command editor facilities make it an easy-to-use tool for developing and debugging PL/SQL blocks.

As part of SQL compatibility enhancements in IBM DB2 9.7 for Linux, UNIX, and Windows, a host of compatibility features enable users to run Oracle applications in the DB2 environment. Part of this delivery was an interactive command-line utility, CLPPlus. CLPPlus is a unified command-line tool for all IBM data servers, including DB2 for Linux, UNIX, and Windows, DB2 for z/OS®, and Informix. Along with its support for SQL and native server commands, CLPPlus has evolved over time, bridging the SQL compatibility gap that migrating customers encountered and reported to help us mature CLPPlus. Now CLPPlus is capable of handling the migrating SQL scripts with comprehensive support for SQL compatibility features, including:

  • PL/SQL dialect
  • Report formatting
  • Scripting
  • AUTOTRACE
  • SQL Buffer Utility
  • Spooling

The following sections provide an overview of each of these features with examples.


Starting CLPPlus

CLPPlus offers several options you can specify when you invoke it from the command line. This includes enabling silent mode, connecting using a configuration file, and running SQL scripts. These options provide the flexibility to invoke CLPPlus to meet your needs.

SQL scripts can be run during CLPPlus startup using the @ command, which is helpful for running routine batch jobs when CLPPlus is started.

Starting CLPPlus with -s[ilent] suppresses certain messages, such as version information, copyright information, prompt messages, and connection information, from displaying on the console. These options can be used in combinations. Invoke CLPPlus in silent mode, executing demo.sql file using the @ command: clpplus -silent @C:\demo.sql.

A connection string can be specified during CLPPlus invocation in order to establish the database connection in CLPPlus session as shown in the example below. CLPPlus can even be specified to pick up the connection information from the configuration file by providing the dsn_alias name during invocation. The example below shows how to invoke CLPPlus with a database connection string.

Listing 1. Invoking CLPPlus with connection information
C:\>clpplus -nw manshanb/temp4now@localhost:500000.testdb

CLPPlus: Version 1.4
Copyright >c< 2009, IBM CORPORATION. All rights reserved.

Database Connection Information:
Hostname = localhost
Database server = DB2/NT SQL09074
SQL authorizaiton ID = manshanb
Local database alias = TESTDB
Port = 50000

SQL>

Starting CLPPlus in verbose mode (-verbose) enables the printing of additional messages in the CLPPlus session, which are suppressed in normal mode. This will provide additional information to the user, which is especially helpful in case of failure of any command execution in CLPPlus.

Input and output redirection is supported in CLPPlus, where a user can redirect a file to CLPPlus session to provide input values, which is useful in running SQL scripts without manual intervention when script demands input values to be provided during runtime. Users can even redirect CLPPlus output to a file using output redirection in CLPPlus. The example below shows the usage of input redirection in CLPPlus. File input.txt is being passed to the CLPPlus session, which contains the input value required by demo.sql script.

Listing 2. Input redirection in CLPPlus
C:\>clpplus @c:\demo.sql <c:\input.txt  

CLPPlus: Version 1.4
Copyright >c< 2009, IBM CORPORATION. All rights reserved.

Database Connection Information:
Hostname = localhost
Database server = DB2/NT SQL09074
SQL authorization ID = manshanb
Local database alias = SAMPLE
Port = 50000

Enter a value for variable 1:
Original statement: select * from employee where firstnme='&1'
New statement with substitutions: select * from employee where firstnme='MICHAEL'

FIRSTNME                                        SALARY
-------------------------- ---------------------------
MICHAEL                                        10000.0
C:\>

Sample data

The example data shown in this article is part of the DB2 SAMPLE database, which comes with the product.

CLPPlus also provides a facility to run user scripts automatically on each CLPPlus log-in. This can be achieved in two ways: either by setting the script file name with path as value to the environment variable CLPPLUS_USER_STARTUP_SCRIPT or by having a script file with name .clpplusrc in the operating system's home directory. This is helpful in executing a set of commands to run at startup on each invocation of CLPPlus.


Support for PL/SQL dialects

CLPPlus supports the execution of all types of PL/SQL statements supported by DB2 for LUW server. Statements like create and drop package, procedure, function, trigger, etc. can be executed seamlessly in CLPPlus. PL/SQL block statements like DECLARE, BEGIN, END can be executed in CLPPlus as anonymous blocks. The following example shows the execution of a simple PL/SQL block in CLPPlus.

Listing 3. PL/SQL block execution
SQL> set serveroutput on
SQL> begin
  2  dbms_output.put_line('plsql demo');
  3  end;
  4  /

plsql demo

DB250000I: The command completed successfully.

Anonymous blocks can also be executed using the execute command in CLPPlus. The execute command is mainly used for the execution of single-line PL/SQL statements as shown in the example below. This command eases the execution of single-line PL/SQL statements by avoiding the need for wrapping them between BEGIN-END blocks. In the example below, the variable var1 is assigned a varchar value using the exec command.

Listing 4. Single-line PL/SQL execution
SQL> variable var1 varchar
DB250000I: The command completed successfully.

SQL> exec :var1:='manshanb';
DB250000I: The command completed successfully.

PL/SQL procedures can be executed in CLPPlus by using the CALL command. The exec command can also be used to execute procedures as shown in the example below.

Listing 5. PL/SQL execution using call and exec commands
SQL> call dbms_output.put_line('plsql demo');
plsql demo
DB250000I: The command completed successfully.

SQL> exec dbms_output.put_line('plsql demo');
plsql demo
DB250000I: The command completed successfully.

CLPPlus supports printing of DBMS_OUTPUT message buffer contents onto the CLPPlus console using server output facility. A user can enable this facility by using the set command as shown in the example below. This can be useful in debugging PL/SQL blocks or procedures by fetching server output messages inserted in PL/SQL blocks using the DBMS_OUTPUT.PUT_LINE() statement.

Listing 6. Server output inside PL/SQL block
SQL> set serveroutput on
SQL> Begin
   2	For i In 1..10 Loop
   3		Dbms_Output.Put_Line('Count '||i);
   4 	End Loop;
   5 End;
   6 /

Count 1
Count 2
Count 3
Count 4
Count 5
Count 6
Count 7
Count 8
Count 9
Count 10

DB250000I: The command completed successfully.

Report formatting

CLPPlus supports a number of report formatting features, such as column command, set command, page title, report title, break, and compute, which provide advanced report generation and formatting facilities to the user. Users can use these options to customize the report as per the business needs. In this section, we will explore important features supported by CLPPlus.

column command

column provides several options that can be used to configure a particular column in the report. column is defined on a column name that needs to be configured. ON and OFF options can be used to control the enabling of the column command. ? column provides details about all the options supported by the column command.

The format option is used to format the column values of type numeric and varchar. To format the width of a character string, follow the A with an integer. String values exceeding the width specified are wrapped or truncated based on the TRUNCATED or WRAPPED option specified in the column command. Numeric values can be formatted with a format string as shown in the example. The justify option can be used to align the column values to the right, center, or left of a column. The example below shows the usage of these column options with employee table.

Listing 7. Column formatting options
SQL>column salary format 99999.99
SQL>column lastname format A6
SQL>firstnme, lastname, salary from employee ;

FIRSTNME     LASTNA      SALARY
------------ ------ -----------
CHRISTINE    HAAS      ########
MICHAEL      THOMPS    94250.00
             ON

SALLY        KWAN      98250.00
JOHN         GEYER     80175.00
IRVING       STERN     72250.00
EVA          PULASK    96170.00
             I

EILEEN       HENDER    89750.00
             SON

THEODORE     SPENSE    86150.00
             R

VINCENZO     LUCCHE    66500.00
             SSI

SEAN         O'CONN    49250.00
             ELL

Some of the other column options useful in formatting reports are given below. A column heading can be changed with the HEADING option by specifying the heading text. The PRINT | NOPRINT option can be used to control the printing of a particular column. NULL values in a column can be replaced by user-defined text using the NULL TEXT option. FOLD_BEFORE and FOLD_AFTER can be used to feed a new line, before or after printing the column values.

The following example demonstrates the usage of the HEADING option in changing the heading of a column in a table result display. In the example, the FORMAT option is used to format varchar and decimal columns, ENAME, and SAL respectively.

Listing 8. Column formatting options
SQL> COLUMN ENAME HEADING EMP_NAME FORMAT A7 WRAP
SQL> COLUMN SAL FORMAT $9999.99
SQL> select ename, deptno, sal from emptable;

EMP_NAM      DEPTNO         SAL
------- ----------- -----------
SMITH            20     $800.00
ALLEN            30    $1600.00
WARD             30    $1250.00
JONES            20    $2975.00
MARTIN           30    $1250.00
BLAKE            30    ########
CLARK            10    $2450.00
SCOTT            20    $3000.00
KING             10    $5000.00
MANGESH          30    $1500.00
SHA

OLD_VALUE and NEW_VALUE variables are used to hold the old and new values of a column at any point in time during report generation. These values are mainly used with page titles. The LIKE option allows for the copying of format and display attributes between two columns.

Page and report titles

CLPPlus supports the printing of page and report titles, which are useful in report generation. ttitle and BTITLE are used to enable the page top and page bottom titles, respectively. Similarly, REPHEADER and REPFOOTER are used in enabling the printing of header and footer text in reports. Title texts can be aligned to the left, right, and center of the page based on user need. Page titles can also be configured to display the page numbers in the reports using the PGNO option. Column new_value and old_value variable values can be displayed as part of TTITLE and btitle text, respectively. The SKIP n value is used to skip the n number of lines after the title display. The example below shows the usage of page titles.

Listing 9. Page titles
SQL> ttitle right 'Employee Data'
SQL> btitle center 'IBM Corporation'
SQL> select empno, firstnme, lastname, edlevel, salary, bonus from emp ;
SQL> set linesize 70

                                                        Employee Data

EMPNO  FIRSTNME     LASTNAME        EDLEVEL      SALARY       BONUS
------ ------------ --------------- ------- ----------- -----------
000010 CHRISTINE    HAAS                 18   152750.00     1000.00
000020 MICHAEL      THOMPSON             18    94250.00      800.00
000030 SALLY        KWAN                 20    98250.00      800.00
000050 JOHN         GEYER                16    80175.00      800.00
000060 IRVING       STERN                16    72250.00      500.00
000070 EVA          PULASKI              16    96170.00      700.00
000090 EILEEN       HENDERSON            16    89750.00      600.00
000100 THEODORE     SPENSER              14    86150.00      500.00
000110 VINCENZO     LUCCHESSI            19    66500.00      900.00
000120 SEAN         O'CONNELL            14    49250.00      600.00
000130 DELORES      QUINTANA             16    73800.00      500.00

                            IBM Corporation

                                                        Employee Data

EMPNO  FIRSTNME     LASTNAME        EDLEVEL      SALARY       BONUS
------ ------------ --------------- ------- ----------- -----------
000210 WILLIAM      JONES                17    68270.00      400.00
000220 JENNIFER     LUTZ                 18    49840.00      600.00
000230 JAMES        JEFFERSON            14    42180.00      400.00
000240 SALVATORE    MARINO               17    48760.00      600.00
000250 DANIEL       SMITH                15    49180.00      400.00
000260 SYBIL        JOHNSON              16    47250.00      300.00
000270 MARIA        PEREZ                15    37380.00      500.00
000280 ETHEL        SCHNEIDER            17    36250.00      500.00
000290 JOHN         PARKER               12    35340.00      300.00
000300 PHILIP       SMITH                14    37750.00      400.00
000310 MAUDE        SETRIGHT             12    35900.00      300.00

                            IBM Corporation

BREAK and COMPUTE

The two important commands used for the report generation are BREAK and COMPUTE. These commands are used together during report generation to format the report based on column values. Using them allows reports to be broken down into multiple sets of related records, making a report more readable and sorted.

The BREAK command can be used to break the report into a set of records based on a particular column value. COMPUTE can be used to perform some computation on that set of records. The following example shows the usage of these commands in CLPPlus.

Listing 10. BREAK and COMPUTE commands
SQL> BREAK ON EDLEVEL SKIP 2

SQL> COMPUTE MAX LABEL MAX_SAL of SALARY on EDLEVEL

SQL> SELECT EDLEVEL, EMPNO, FIRSTNME, SALARY FROM EMPLOYEE ORDER BY EDLEVEL;

EDLEVEL EMPNO  FIRSTNME          SALARY
------- ------ ------------ -----------
     12 000290 JOHN            35340.00
     12 000310 MAUDE           35900.00
     12 200310 MICHELLE        35900.00
*******                     -----------
MAX_SAL                        35900.00



     14 000100 THEODORE        86150.00
     14 000120 SEAN            49250.00
     14 000230 JAMES           42180.00
     14 200120 GREG            39250.00
     14 200330 HELENA          35370.00
*******                     -----------
MAX_SAL                        86150.00
...
...

For more information about CLPPlus report formatting features, see the article "Generate formatted reports for IBM data servers using CLPPlus."


Scripting support

Features like bind variables, substitution variables, WHENEVER SQLERROR, WHENEVER OSERROR, etc. together constitute the scripting support in CLPPlus. We can use these features in developing SQL scripts and, thus, seamlessly use variables across client and servers to control the flow of script execution based on the action specified for the occurrences of SQL and OS errors. Environment variables can also be accessed within CLPPlus scripts in a like way across operating systems. In this section, we will walk through some of these features supported by CLPPlus.

Bind variables

Support for bind variables is a unique feature in CLPPlus. A variable is declared on the CLPPlus client, and can be used as server variable in SQL and PL/SQL blocks. The exec command can be used to initialize the variable with a value. It can also be initialized by wrapping it within Begin-End blocks. CLPPlus supports variables of various data types, including integer, decimal, varchar, varchar2, number, Boolean, row, array of rows, cursor, etc. Its ability to be used seamlessly across client and server means you can use the same variable across multiple executions. The PRINT command can be used to display the variable value in CLPPlus.

Listing 11. Bind variables
SQL> variable var1 varchar2
DB250000I: The command completed successfully.

SQL> variable var2 number
DB250000I: The command completed successfully.

SQL> exec :var1:='manshanb' ;
DB250000I: The command completed successfully.

SQL> begin
  2  :var2:=15500.50;
  3  end;
  4  /

DB250000I: The command completed successfully.

SQL> print var1
'manshanb'

SQL> print var2
15500.50

SQL> select firstnme from employee where salary=:var2 ;

FIRSTNME            
---------------
Michael

Variables are effective in procedure calls, where you can capture the values of OUT parameters into a variable and we can pass a variable as an INPUT parameter value to a procedure call, as shown in the example below. INOUT parameters in procedure calls can be handled using single variable that will behave as both IN and OUT.

Listing 12. Bind variables in procedure calls
SQL> CREATE OR REPLACE PROCEDURE TESTPROC ( IN P1 INTEGER, INOUT P2 INTEGER, 
   - OUT P3 VARCHAR2(20))
   2 BEGIN
   3 	SET P2 = P1+P2 ;
   4	SET P3 = 'Result is ' || P2;	
   5 END ;
   6 /

DB250000I: The command completed successfully.

SQL>  var p1 integer
DB250000I: The command completed successfully.

SQL>  var p2 integer
DB250000I: The command completed successfully.

SQL>  var p3 varchar2
DB250000I: The command completed successfully.

SQL> exec :p1:=100;
DB250000I: The command completed successfully.

SQL> exec :p2:=50;
DB250000I: The command completed successfully.

SQL> call testproc(:p1, :p2, :p3);
DB250000I: The command completed successfully.

SQL> print p1
100

SQL> print p2
150

SQL> print p3
'Result is 150'

Substitution variables

Unlike bind variables, a substitution variable is processed on the client side, and its value will be substituted before submitting the command for execution. It can be used as placeholder to store the string values, which can be used later until the session is alive. Substitution variables can be defined using the define command, and is accessed using the ampersand (&) character, followed by variable name. The example below shows the usage of substitution variables in CLPPlus.

Listing 13. Substitution variable demonstration
SQL> define name='MICHAEL'

SQL> select firstnme, salary from employee where firstnme='&name';

Original statement:select firstnme, salary from employee where firstnme='&name'
New statement with substitutions:select firstnme, salary from employee where 
firstnme='MICHAEL'

FIRSTNME              SALARY
--------------- ------------
MICHAEL                10000

If an accessed variable is not defined, CLPPlus prompts for the value for that variable, as shown below.

Listing 14. Substitution variable demonstration
SQL> select firstnme, salary from employee where salary > &sal;
Enter a value for variable sal: 15000

Original statement:select firstnme, salary from employee where salary > &sal
New statement with substitutions:select firstnme, salary from employee where 
salary > 15000

FIRSTNME                              SALARY
-------------------- -----------------------
MANSHANB                               15500
JOE                                    25000

Users can also accept a value for a variable during runtime by using the ACCEPT command as shown in the example below. This is useful in prompting a value for a variable, which needs input from the user during dynamic execution of the script.

Listing 15. ACCEPT
SQL> accept USER_ID
Enter a value for variable USER_ID: manshanb

SQL> define
DEFINE USER_ID = manshanb

CLPPlus also supports passing of Environment or Shell variables as arguments to the scripts during CLPPlus invocation, which are then converted into substitution variables named after their positions, which can be accessed using the ampersand (&) character, followed by their position. Similarly even values can be passed as arguments to the scripts. The example below demonstrates this feature.

Listing 16. Shell variables as substitution variables
C:\>Set DEPT=E32 

C:\>clpplus -s @c:\demo.sql %DEPT% 10000

Original statement:select firstnme, salary from employee where edlevel='&1' and 
salary > &2
New statement with substitutions:select firstnme, salary from employee where edlevel='E32'
and salary > 10000

FIRSTNME                 SALARY
--------------- ---------------
MICHAEL                   15500
JOE                       11100
manshanb                  20000

demo.sql contains the following SQL statement:

select firstnme, salary from employee where edlevel='&1' and salary > &2 ;

The environment variable DEPT and the value 10000 have been passed as arguments to script demo.sql, which consumes these arguments in the form of substitution variables.

Error handling

Errors occurred during the execution of scripts in CLPPlus can be trapped and a specific action can be performed by using WHENEVER SQLERROR and WHENEVER OSERROR commands supported. Using these commands, we can control the behavior of CLPPlus by specifying the actions to be performed like EXIT or CONTINUE whenever an error occurs. We can also choose to commit or roll back the uncommitted transactions during SQL or OS errors using COMMIT or ROLLBACK options. In the case of EXIT, we can return the operating system-dependent return code based on the error scenario.

The following example shows the WHENEVER SQLERROR CONTINUE command behavior. Here on error, the CLPPlus prompt is returned and continued to wait for user input.

Listing 17. WHENEVER SQLERROR
 SQL> whenever sqlerror continue

SQL> select * from nonexistingtable;
SQL0204N "SCHEMA.NONEXISTINGTABLE" is an undefined name.

SQL>

We can also commit, roll back, or take no action whenever a SQL error occurs.

Listing 18. WHENEVER SQLERROR action types
SQL> whenever sqlerror continue commit 

SQL> whenever sqlerror continue rollback

SQL> whenever sqlerror continue none

The following example demonstrates the usage of the EXIT option during a SQL error to exit the CLPPlus application.

Listing 19. WHENEVER SQLERRORexit action
SQL> whenever sqlerror exit

SQL> select * from nonexistingtable;
SQL0204N "SCHEMA.NONEXISTINGTABLE" is an undefined name.

C:\>

The following example specifies the error code returned during EXIT. This behavior is identical to the EXIT CLPPlus command.

Listing 20. EXIT code during WHENEVER SQLERROR
SQL> whenever sqlerror exit failure

SQL> select * from nonexistingtable;
SQL0204N "SCHEMA.NONEXISTINGTABLE" is an undefined name.

C:\echo %errorlevel%
1

Users can also specify a substitution variable value to be returned during exit. The following example demonstrates this behavior.

Listing 21. Substitution variable value returned during WHENEVER SQLERROR
SQL> define exit_value=6

SQL> whenever sqlerror exit exit_value

SQL> select * from nonexistingtable;
SQL0204N "SCHEMA.NONEXISTINGTABLE" is an undefined name.

C:\echo %errorlevel%
6

As supported by the EXIT CLPPlus command, we can specify whether to commit or roll back the transactions whenever error occurs. The following commands demonstrate the commit and rollback actions with WHENEVER SQLERROR:

whenever sqlerror exit 2 commit 
whenever sqlerror continue rollback

The following example shows the CLPPlus behavior during an OS error with exit error code.

Listing 22. WHENEVER OS ERROR
SQL> whenever oserror exit -1

SQL> get c:\nonexistingfile.sql
DB250204E: An attempt to locate a file 'c:\\nonexistingfile.sql' failed. The command 
cannot be processed.

c:\>echo %errorlevel%
-1

The following example shows the command behavior when CONTINUE is specified with the COMMIT option.

Listing 23. WHENEVER OS ERROR with CONTINUE COMMIT
SQL> whenever oserror continue commit

SQL> get c:\nonexistingfile.sql
DB250204E: An attempt to locate a file 'c:\\nonexistingfile.sql' failed. The command 
cannot be processed.

SQL>

AUTOTRACE

AUTOTRACE provides information about the access plan, that optimizer chooses during the execution of a statement, along with some key statistics values for that particular execution recorded by Server. AUTOTRACE output can be used to optimize the SQL queries based on analysis of the data, which is useful in performance tuning of SQL queries, or in diagnosing the variation in application performances.

SET AUTOT[RACE] { OFF | ON | TRACE[ONLY] } [EXP[LAIN]] [STAT[ISTICS]]

Setting the AUTOTRACE option ON will enable the display of explain and statistics information for each statement executed in that session along with query results, until the AUTOTRACE is disabled. EXPLAIN and STATISTICS options enable the display of explain plan and statistics information, respectively. Statistics information include network statistics, Cost, Lock information, Sorts, Pool I/O, Rows read, Rows modified and Rows returned, etc. to name a few. The TRACEONLY option disables the display of query execution results.

The example given below shows the output for AUTOTRACE when set to ON.

Listing 24. AUTOTRACE
SQL> SET AUTOTRACE ON

SQL> SELECT EMPNO, FIRSTNME, LASTNAME, WORKDEPT, PHONENO FROM EMPLOYEE 
     WHERE SALARY=152750 ;

EMPNO  FIRSTNME     LASTNAME        WORKDEPT PHONENO
------ ------------ --------------- -------- -------
000010 CHRISTINE    HAAS            A00      3978   



EXPLAIN PLAN
-------------------------

    ID TYPE            OBJECT_SCHEMA        OBJECT_NAME         
------ --------------- -------------------- --------------------
PREDICATE_TEXT                                    
--------------------------------------------------
     1 RETURN                                                   
                                                  

     2 TBSCAN          MANSHANB             EMPLOYEE            
(Q1.SALARY = +0152750.00)                         




STATISTICS
---------------------------------
COMPILATION_TIME      =  195000
ISOLATION_LEVEL       =  CS
ROWS_READ             =  42
ROWS_RETURNED         =  1
ROWS_MODIFIED         =  0
TOTAL_SORTS           =  0
IN_MEMORY_SORTS       =  0
LOCK_WAITS            =  0
LOCK_ESCALS           =  0
LOCK_TIMEOUTS         =  0
POOL_READS            =  1
POOL_WRITES           =  0
QUERYOPT              =  5
ESTIMATED_COST        =  8
TOTAL_COST            =  7.6209
NETWORK_TRIPS         =  2
BYTES_SENT            =  505
BYTES_RECEIVED        =  1215

The example below shows the usage of AUTOTRACE with the EXPLAIN plan enabled, along with query results. This disables the display of statistics information.

Listing 25. AUTOTRACE with EXPLAIN enabled
SQL> SET AUTOTRACE ON EXPLAIN

SQL> SELECT EMPNO, FIRSTNME, LASTNAME FROM EMPLOYEE WHERE SALARY=152750 ;

EMPNO  FIRSTNME     LASTNAME       
------ ------------ ---------------
000010 CHRISTINE    HAAS           


EXPLAIN PLAN
-------------------------

    ID TYPE            OBJECT_SCHEMA        OBJECT_NAME         
------ --------------- -------------------- --------------------
PREDICATE_TEXT                                    
--------------------------------------------------
     1 RETURN                                                   
                                                  

     2 TBSCAN          MANSHANB             EMPLOYEE            
(Q1.SALARY = +0152750.00)

The example below demonstrates the usage of AUTOTRACE with TRACEONLY option. The Query result display is disabled with this setting.

Listing 26. AUTOTRACE with TRACEONLY mode
SQL> SET AUTOTRACE TRACEONLY

SQL> SELECT * FROM EMPLOYEE WHERE SALARY=152750 ;


EXPLAIN PLAN
-------------------------

    ID TYPE            OBJECT_SCHEMA        OBJECT_NAME         
------ --------------- -------------------- --------------------
PREDICATE_TEXT                                    
--------------------------------------------------
     1 RETURN                                                   
                                                  

     2 TBSCAN          MANSHANB             EMPLOYEE            
(Q1.SALARY = +0152750.00)                         

STATISTICS
---------------------------------
COMPILATION_TIME      =  4000
ISOLATION_LEVEL       =  CS
ROWS_READ             =  42
ROWS_RETURNED         =  1
ROWS_MODIFIED         =  0
TOTAL_SORTS           =  0
IN_MEMORY_SORTS       =  0
LOCK_WAITS            =  0
LOCK_ESCALS           =  0
LOCK_TIMEOUTS         =  0
POOL_READS            =  1
POOL_WRITES           =  0
QUERYOPT              =  5
ESTIMATED_COST        =  8
TOTAL_COST            =  7.6209
NETWORK_TRIPS         =  2
BYTES_SENT            =  462
BYTES_RECEIVED        =  2364

SQL Buffer Utility

This built-in editor facility supported by CLPPlus can be used in developing large SQL or PL/SQL blocks. It provides different buffer utilities, which can be handy in editing, saving, and executing the SQL Buffer Utility contents quickly and effectively. The SQL Buffer Utility can be useful in:

  • Modifying the SQL Buffer Utility contents by deleting, inserting, or replacing a line in the buffer
  • Loading a file contents into the SQL buffer
  • Editing the buffer contents using external editor
  • Executing the buffer contents
  • Saving the buffer contents into a file

Any previously executed SQL or PL/SQL command in CLPPlus is stored in the SQL Buffer Utility. The LIST command can be used to list the SQL buffer contents on to the console. If user wants to see only a particular line in the buffer, then LIST followed by line number will display that line contents on to the console as shown in the example below. Just entering the line number will also list the contents of the buffer at the given line number.

Listing 27. LIST command
SQL> select * from employee
  2  where firstnm='MICHAEL';
ERROR near line 1:
SQL0206N  "FIRSTNM" is not valid in the context where it is used.


SQL> list
 1  select * from employee
 2* where firstnm='MICHAEL'


SQL> list 2
 2* where firstnm='MICHAEL'

SQL> 1
 1* select * from employee

A particular string in the SQL Buffer Utility can be replaced using the CHANGE command. We can also replace the text in the buffer at a given line number or remove the first occurrence of a string in the current line, using this utility.

The example below replaces the token firstnm with firstnme using the CHANGE command.

Listing 28. CHANGE command
SQL> list
 1  select * from employee
 2* where firstnm='MICHAEL'

SQL> change /firstnm/firstnme/
 2* where firstnme='MICHAEL'

In the example below, the text at line 2 is replaced with new line of text. Here, 2 represents the line number to be replaced in the buffer, with the text followed thereafter.

Listing 29. Line replacement using line number
SQL> 2 where salary>10000
 2* where salary>10000

SQL> list
 1  select * from employee
 2* where salary>10000

We can append text to SQL Buffer Utility contents or add text into a new line in the buffer using APPEND and INPUT commands, respectively. The DEL utility can be used to delete a particular line in the buffer. The examples below demonstrate these utilities.

Listing 30. INPUT, APPEND, and DEL
SQL> input select * from employee
SQL> list
 1* select * from employee

SQL> append  where
SQL> list
 1* select * from employee where

SQL> input salary>10000
SQL> list
 1  select * from employee where
 2* salary>10000

SQL> del 2

SQL> list
 1* select * from employee where

SQL> input firstnme='MICHAEL'

SQL> list
 1  select * from employee where
 2* firstnme='MICHAEL'

CLPPlus supports loading a file contents into the SQL Buffer Utility or saving the buffer contents into a file, which is useful in developing SQL scripts across multiple sessions or across different machines. The SAVE utility can be used in saving the buffer contents into a file, and the GET utility can be used in loading a file contents into the SQL Buffer Utility.

Listing 31. GET and SAVE utilities
SQL> get c:\buffer.sql
 1* select * from employee

SQL> input where salary > 10000

SQL> list
 1  select * from employee
 2* where salary > 10000

SQL> save c:\buffer1.sql
DB250000I: The command completed successfully.

The backslash (/) command executes the contents present in the SQL Buffer Utility. And the CLEAR command clears the SQL Buffer Utility contents.

Listing 32. Clear buffer utility
SQL> list
 1* select firstnme, salary from employee fetch first 2 rows only

SQL> /

FIRSTNME                         SALARY
--------------- -----------------------
MICHAEL                           15500
JOE                               11100

SQL> clear buffer
DB250000I: The command completed successfully.

SQL> list
DB250419W: The SQL buffer is empty.

SQL Buffer Utility contents can also be edited using an external editor with the help of the EDIT command. EDIT invokes the external editor with SQL Buffer Utility contents loaded into it, which can be modified by the user and be saved back into the buffer. Users can configure the external editor to be used as per the requirement.


Spooling

Activities occurred in a CLPPlus session can be recorded into a file using the spooling feature. This will be helpful in developing SQL scripts out of commands executed in a CLPPlus session. As a result of this, all the commands executed and the output are written to a file that can be processed to develop the script files.

A file needs to be specified while enabling spooling, in which the display output is written. Spooling can also be turned off at any point in a session, as shown below.

Listing 33. spool command
SQL> spool c:\spooler.sp

SQL> select firstnme, salary from employee fetch first 2 rows only;

FIRSTNME                         SALARY
--------------- -----------------------
MICHAEL                           15500
JOE                               11100

SQL> spool off

Conclusion

This article has provided value for users migrating from Oracle to DB2 by giving an overview of the SQL compatibility support provided in CLPPlus. CLPPlus plays an important role in enabling the migrating of user scripts to DB2, and, hence, this article can be a good reference point for getting a broad picture of supported SQL compatibility features in CLPPlus.

This article should also be helpful for existing DB2 users in helping them become familiar with the facilities provided by CLPPlus, which are new to DB2 command-line tools, and can help them in performing their tasks in a much better way.

To know more about CLPPlus and its supported commands, visit the DB2 for Linux, UNIX, and Windows V9.7 Information Center.

Resources

Learn

Get products and technologies

  • Download DB2 Express-C, the free edition of DB2 database for Linux, Windows, Solaris, and Mac that's easy to use and provides a solid base for building and deploying applications.
  • Download an evaluation copy DB2 for Linux, UNIX, and Windows to try it out in your own environment
  • Build your next development project with IBM trial software, available for download directly from developerWorks.

Discuss

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=754951
ArticleTitle=SQL compatibility in CLPPlus
publish-date=09012011