DB2 Two "how to" questions - [conditional loading and variable use]

[jcolino]

Hi,

I usually get stuck with one of two questions that keep coming up.
Perhaps some of you brilliant people can shed some light...
1. How to do use if statements in load commands (I usually need this
in the task center):
if (select count(*) from mytable 1000) then load from file

2. How to create temporary variables that I can reuse over several
places within a single SQL statement:
myvar = 100;
select * from mytable where column1 = myvar and column2 = myvar and
column3 = myvar;

Thanks!

[Dave Hughes]

On Mon, 13 Aug 2007 14:53:27 -0700, jcolino scribbled:

Hi,

I usually get stuck with one of two questions that keep coming up.
Perhaps some of you brilliant people can shed some light...
1. How to do use if statements in load commands (I usually need this in
the task center):
if (select count(*) from mytable 1000) then load from file

2. How to create temporary variables that I can reuse over several
places within a single SQL statement: myvar = 100;
select * from mytable where column1 = myvar and column2 = myvar and
column3 = myvar;

In answer to both questions I'd say as far as DB2's SQL dialect goes: you
can't. Okay, that's not strictly true. I think there are ways doing this
in DB2 with "pure" SQL, but still it's not the "right" way of going about
this.

Generally, the philosophy in DB2 seems to be "leave the control flow and
global stuff to some higher layer", e.g. a shell script running DB2
commands via the CLP (Command Line Processor). The CLP makes this very
easy by enabling persistant connections between calls. For example, a
simple chunk of shell-script under Linux/UNIX:
#!/bin/sh

myvar = 100

db2 CONNECT TO MYDB
if [[ $(db2 "SELECT COUNT(*) FROM MYTABLE WHERE MYCOL 1000") -ne 0 ]]
then
db2 "LOAD FROM MYFILE.IXF OF IXF INSERT INTO MYTABLE"
fi

db2 "SELECT * FROM MYTABLE WHERE COLUMN1 = $myvar AND COLUMN2 = $myvar
AND COLUMN3 = $myvar"
This is rather more difficult on Windows (primarily because CMD is so
useless as a scripting environment), in which case I'd recommend a decent
scripting language like Perl/Ruby/Python instead.

If you're absolutely determined to do this in pure SQL, you'll probably
need to do it with a stored procedure (one that uses the ADMIN_CMD
procedure to run the LOAD command in ths first case). For example (these
are off the top of my head - untested):

--#SET TERMINATOR @

/* Drop the procedures if they already exist */
DROP SPECIFIC PROCEDURE TASK1@
DROP SPECIFIC PROCEDURE TASK2@

/* Procedure for the first task */
CREATE PROCEDURE TASK1()
SPECIFIC TASK1
LANGUAGE SQL
BEGIN
DECLARE I INTEGER DEFAULT 0;
/* Capture the value returned by the query into I */
SELECT COUNT(*) INTO I FROM MYTABLE WHERE COL1 1000;
/* Conditionally execute the load, depending on I */
IF I <0 THEN
CALL SYSPROC.ADMIN_C MD('LOAD FROM MYFILE.IXF OF IXF INSERT INTO
MYTABLE');
END IF;
END@

/* Procedure for the second task */
CREATE PROCEDURE TASK2()
SPECIFIC TASK2
/* Declare that the procedure returns a result set */
RESULT SETS 1
LANGUAGE SQL
BEGIN
/* Declare the "global" variable (actually a variable local to the
prcoedure body in this case */
DECLARE MYVAR INTEGER;
/* Declare a cursor for the result set */
DECLARE C CURSOR WITH RETURN FOR
SELECT *
FROM MYTABLE
WHERE COLUMN1 = MYVAR
AND COLUMN2 = MYVAR
AND COLUMN3 = MYVAR;
/* Set the global variable to a value */
SET MYVAR = 100;
/* Open the cursor for the return query (it'll use the current value of
MYVAR when its run). Note that the cursor is left open to return the
result set */
OPEN C;
END@

CALL TASK1@
CALL TASK2@

Note that the statement terminator in the above is @ to permit ; to be
used as the terminator within the procedure bodies.
Cheers,

Dave.

[Mark A]

<jc*****@gmail. comwrote in message
news:11******** **************@ o61g2000hsh.goo glegroups.com.. .

Hi,

I usually get stuck with one of two questions that keep coming up.
Perhaps some of you brilliant people can shed some light...
1. How to do use if statements in load commands (I usually need this
in the task center):
if (select count(*) from mytable 1000) then load from file

2. How to create temporary variables that I can reuse over several
places within a single SQL statement:
myvar = 100;
select * from mytable where column1 = myvar and column2 = myvar and
column3 = myvar;

Thanks!

You can use control logic and define variables within a "begin atomic"
block.

However, since a load is not SQL (it is a command), you cannot include it
within an SQL statement. But you can call a stored procedure that causes a
load to occur if you have DB2 version 9.

DB2 version 8.2.2 support the following DB2 commands in the procedure
SYSPROC.ADMIN_C MD.

DESCRIBE
EXPORT
PRUNE HISTORY/LOGFILE
REORG INDEXES/TABLE
RUNSTATS
UPDATE DATABASE CONFIGURATION

DB2 V9 supports these DB2 commands to SYSPROC.ADMIN_C MD:

ADD CONTACT
ADD CONTACTGROUP
AUTOCONFIGURE
BACKUP - online only
DESCRIBE
DROP CONTACT
DROP CONTACTGROUP
EXPORT
FORCE APPLICATION
IMPORT
INITIALIZE TAPE
LOAD
PRUNE HISTORY/LOGFILE
QUIESCE DATABASE
QUIESCE TABLESPACES FOR TABLE
REDISTRIBUTE
REORG INDEXES/TABLE
RESET ALERT CONFIGURATION
RESET DATABASE CONFIGURATION
RESET DATABASE MANAGER CONFIGURATION
REWIND TAPE
RUNSTATS
SET TAPE POSITION
UNQUIESCE DATABASE
UPDATE ALERT CONFIGURATION
UPDATE CONTACT
UPDATE CONTACTGROUP
UPDATE DATABASE CONFIGURATION
UPDATE DATABASE MANAGER CONFIGURATION
UPDATE HEALTH NOTIFICATION CONTACT LIST
UPDATE HISTORY

[Serge Rielau]

myvar = 100;

select * from mytable where column1 = myvar and column2 = myvar and
column3 = myvar;

WITH variables(myvar ) AS VALUES (100)
SELECT * from mytable, variables where column1 = myvar and column2 =
myvar and column3 = myvar;

DB2 will unravel it, so no harm done...

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Dave Hughes]

On Mon, 13 Aug 2007 23:15:20 -0400, Serge Rielau scribbled:

>myvar = 100;
select * from mytable where column1 = myvar and column2 = myvar and
column3 = myvar;

WITH variables(myvar ) AS VALUES (100) SELECT * from mytable, variables
where column1 = myvar and column2 = myvar and column3 = myvar;

DB2 will unravel it, so no harm done...

Ooops - that's me not reading the original question properly. I'd assumed
the variable was required across *multiple* SQL statements, hence the
stored proc, when only a single statement was needed - in which case the
CTE with a VALUES statement is indeed a much better solution.

Cheers,

Dave.

[jcolino]

Excellent answers\explana tions. Thanks.