The IMPORT command isn't SQL;

ref

The IMPORT command isn't SQL; it's a "CLP command" (which is why it
doesn't appear under the SQL reference in the InfoCenter, but in a
separate section along with other commands like CREATE DATABASE, LOAD,
etc.)

Before DB2 9, you could only execute CLP commands from the CLP (the db2
command line executable), with the exception of one or two commands.
Starting with DB2 9, you can execute many (but not all) CLP commands
via the ADMIN_CMD() [1] stored procedure, including IMPORT [2], EXPORT
[3] and LOAD [4].

However, one important thing to be aware of with ADMIN_CMD(). The
procedure runs the command *on the server*. Hence, for IMPORT, the
input data file must reside on the server (see [2]).

There's probably some "proper" JDBC method for calling stored
procedures, or I suspect you could just do:

statement.execute("CALL SYSPROC.ADMIN_CMD('IMPORT FROM
''C:/staff.xml.xsd'' OF DEL METHOD P(1) MESSAGES ''C:/messages.txt''
INSERT INTO STAFF_AS_XML (STAFF_AS_XML_COL)')");

[1]
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.l
uw.sql.rtn.doc/doc/r0012547.html

[2]
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.l
uw.sql.rtn.doc/doc/r0023575.html

[3]
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.l
uw.sql.rtn.doc/doc/r0023573.html

[4]
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.l
uw.sql.rtn.doc/doc/r0023577.html

Cheers,

db2 how to

DB2 HOW TOs ref


Start an instance

As an instance owner on the host running db2, issue the following command

$ db2start

Stopping the instance

$ db2stop

Connect to the database as instance owner

$ db2

as a user of the database:

$source ~instance/sqllib/db2cshrc (csh users)

$ . ~instance/sqllib/db2profile (sh users)

$ db2 connect to databasename

Create a table

$ db2-> create table employee

(ID SMALLINT NOT NULL,

NAME VARCHAR(9),

DEPT SMALLINT CHECK (DEPT BETWEEN 10 AND 100),

JOB CHAR(5) CHECK (JOB IN ('Sales', 'Mgr', 'Clerk')),

HIREDATE DATE,

SALARY DECIMAL(7,2),

COMM DECIMAL(7,2),

PRIMARY KEY (ID),

CONSTRAINT YEARSAL CHECK (YEAR(HIREDATE) > 1986 OR SALARY > 40500) )


A simple version:

db2-> create table employee ( Empno smallint, Name varchar(30))

Create a schema

If a user has SYSADM or DBADM authority, then the user can create a schema with any valid name. When a database is created, IMPLICIT_SCHEMA authority is granted to PUBLIC (that is, to all users). The following example creates a schema for an individual user with the authorization ID 'joe'

CREATE SCHEMA joeschma AUTHORIZATION joe

Create an alias

The following SQL statement creates an alias WORKERS for the EMPLOYEE table:

CREATE ALIAS WORKERS FOR EMPLOYEE

You do not require special authority to create an alias, unless the alias is in a schema other than the one owned by your current authorization ID, in which case DBADM authority is required.

Create an Index:

The physical storage of rows in a base table is not ordered. When a row is inserted, it is placed in the most convenient storage location that can accommodate it. When searching for rows of a table that meet a particular selection condition and the table has no indexes, the entire table is scanned. An index optimizes data retrieval without performing a lengthy sequential search. The following SQL statement creates a

non-unique index called LNAME from the LASTNAME column on the EMPLOYEE table, sorted in ascending order:

CREATE INDEX LNAME ON EMPLOYEE (LASTNAME ASC)

The following SQL statement creates a unique index on the phone number column:

CREATE UNIQUE INDEX PH ON EMPLOYEE (PHONENO DESC)

Drop a database:

Db2 drop database sample

Alter tablespace

Adding a Container to a DMS Table Space You can increase the size of a DMS table space (that is, one created with the MANAGED BY DATABASE clause) by adding one or more containers to the table

space. The following example illustrates how to add two new device containers (each with 40 000 pages) to a table space on a UNIX-based system:

ALTER TABLESPACE RESOURCE

ADD (DEVICE '/dev/rhd9' 10000,

DEVICE '/dev/rhd10' 10000)

The following SQL statement drops the table space ACCOUNTING:

DROP TABLESPACE ACCOUNTING

You can reuse the containers in an empty table space by dropping the table space but you must COMMIT the DROP TABLESPACE command, or have had AUTOCOMMIT on, before attempting to reuse the containers. The following SQL statement creates a new temporary table space called TEMPSPACE2:

CREATE TEMPORARY TABLESPACE TEMPSPACE2 MANAGED BY SYSTEM USING ('d')

Once TEMPSPACE2 is created, you can then drop the original temporary table space TEMPSPACE1 with the command: DROP TABLESPACE TEMPSPACE1

Add Columns to an Existing Table

When a new column is added to an existing table, only the table description in the system catalog is modified, so access time to the table is not affected immediately. Existing records are not physically altered

until they are modified using an UPDATE statement. When retrieving an existing row from the table, a null or default value is provided for the new column, depending on how the new column was defined. Columns that are added after a table is created cannot be defined as NOT NULL: they must be defined as either NOT NULL WITH DEFAULT or as nullable. Columns can be added with an SQL statement. The following statement uses the ALTER TABLE statement to add three columns to the EMPLOYEE table:

ALTER TABLE EMPLOYEE

ADD MIDINIT CHAR(1) NOT NULL WITH DEFAULT

ADD HIREDATE DATE

ADD WORKDEPT CHAR(3)

GrantPermissions by Users

The following example grants SELECT privileges on the EMPLOYEE table to the user HERON:

GRANT SELECT ON EMPLOYEE TO USER HERON

The following example grants SELECT privileges on the EMPLOYEE table to the group HERON:

GRANT SELECT ON EMPLOYEE TO GROUP HERON

GRANT SELECT,UPDATE ON TABLE STAFF TO GROUP PERSONNL

If a privilege has been granted to both a user and a group with the same name, you must specify the GROUP or USER keyword when revoking the privilege. The following example revokes the SELECT privilege on the EMPLOYEE table from the user HERON:

REVOKE SELECT ON EMPLOYEE FROM USER HERON

To Check what permissions you have within the database

SELECT * FROM SYSCAT.DBAUTH WHERE GRANTEE = USER AND GRANTEETYPE = 'U'

SELECT * FROM SYSCAT.COLAUTH WHERE GRANTOR = USER

At a minimum, you should consider restricting access to the SYSCAT.DBAUTH, SYSCAT.TABAUTH, SYSCAT.PACKAGEAUTH, SYSCAT.INDEXAUTH, SYSCAT.COLAUTH, and SYSCAT.SCHEMAAUTH catalog views. This would prevent information on user privileges, which could be used to target an authorization name for break-in, becoming available to everyone with access to the database. The following statement makes the view available to every authorization name:


GRANT SELECT ON TABLE MYSELECTS TO PUBLIC

And finally, remember to revoke SELECT privilege on the base table:


REVOKE SELECT ON TABLE SYSCAT.TABAUTH FROM PUBLIC

Delete Records from a table

db2-> delete from employee where empno = '001'

db2-> delete from employee

The first example will delete only the records with emplno field = 001 The second example deletes all the records

Import Command

Requires one of the following options: sysadm, dbadm, control privileges on each participating table or view, insert or select privilege, example:

db2->import from testfile of del insert into workemployee

where testfile contains the following information 1090,Emp1086,96613.57,55,Secretary,8,1983-8-14

or your alternative is from the command line:

db2 " import from 'testfile' of del insert into workemployee"

db2 < test.sql where test.sql contains the following line:

db2 import from test file of del insert into workemployee

Load Command:

Requires the following auithority: sysadm, dbadm, or load authority on the database:

example: db2 "load from 'testfile' of del insert into workemployee"

You may have to specify the full path of testfile in single quotes

Authorization Level:

One of the following:

sysadm

dbadm

load authority on the database and

INSERT privilege on the table when the load utility is invoked in INSERT mode, TERMINATE mode

(to terminate a previous load insert operation), or RESTART mode (to restart a previous load insert

operation)

INSERT and DELETE privilege on the table when the load utility is invoked in REPLACE mode,

TERMINATE mode (to terminate a previous load replace operation), or RESTART mode (to restart a

previous load replace operation)

INSERT privilege on the exception table, if such a table is used as part of the load operation.

Caveat:

If you are performing a load operation and you CTRL-C out of it, the tablespace is left in a load pending state. The only way to get out of it is to reload the data with a terminate statement

First to view tablestate:

Db2 list tablespaces show detail will display the tablespace is in a load pending state.

Db2tbst

Here is the original query

Db2 "load from '/usr/seela/a.del' of del insert into A";

If you break out of the load illegally (ctrl-c), the tablespace is left load pending.

To correct:

Db2 "load form '/usr/seela/a.del' of del terminate into A";

This will return the table to it's original state and roll back the entries that you started loading.

If you try to reset the tablespace with quiesce, it will not work . It's an integrety issue

DB2BATCH- command

Reads SQL statements from either a flat file or standard input, dynamically prepares and describes the statements and returns an answer set: Authorization: sysadmin .and Required Connection -None..eg

db2batch -d databasename -f filename -a userid/passwd -r outfile

DB2expln - DB2 SQL Explain Tool

Describes the access plan selection for static SQL statements in packages that are stored in the DB2 common server systems catalog. Given the database name, package name ,package creator abd section

number the tool interprets and describes the information in these catalogs.


DB2exfmt - Explain Table Format Tool

DB2icrt - Create an instance

DB2idrop - Dropan instance

DB2ilist - List instances

DB2imigr - Migrate instances

DB2iupdt - Update instances

Db2licm - Installs licenses file for product ;

db2licm -a db2entr.lic

DB2look - DB2 Statistics Extraction Tool

Generates the updates statements required to make the catalog statistics of a test database match those of a production. It is advantageous to have a test system contain asubset of your production system's data.

This tool queries the system catalogs of a database and outputs a tablespace n table index, and column information about each table in that database Authorization: Select privelege on system catalogs Required

Connection - None. Syntax

db2look -d databasename -u creator -t Tname -s -g -a -p -o

Fname -e -m -c -r -h

where -s : generate a postscript file, -g a graph , -a for all users in the database, -t limits output to a particular tablename, -p plain text format , -m runs program in mimic mode, examples:

db2look -d db2res -o output will write stats for tables created in db

db2res in latex format

db2look -p -a -d db2res -o output - will write stats in plain text format

DB2 -list tablespaces show detail

displays the following information as an example:

Tablespaces for Current Database

Tablespace ID = 0

Name = SYSCATSPACE

Type = System managed space

Contents = Any data

State = 0x0000

Detailed explanation:

Normal

Total pages = 2925

Useable pages = 2925

Used pages = 2925

Free pages = Not applicable

High water mark (pages) = Not applicable

Page size (bytes) = 4096

Extent size (pages) = 32

Prefetch size (pages) = 32

Number of containers = 1


db2tbst - Get tablespace state.

Authorization - none , Required connection none, syntax db2tbst tabpespace-state:The state value is part of the output of list tablespaces example

db2tbst 0X0000 returns state normal

db2tbst 2 where 2 indicates tablespace id 2 will also work


DB2dbdft - environment variable

Defining this environment variable with the database you want to connect to automatically connects you to the database . example setenv db2dbdft sample will allow you to connect to sample by default.

CLP - Command Line Processor Invocation:

db2 starts the command line processor. The clp is used to execute database utilities, sql statements and online help. It offers a variety of command options and can be started in :

1. interactive mode : db2->

2. command mode where each command is prefixed by db2

3. batch mode which uses the -f file input option


Update the configuration in the database :

Db2 =>update db cfg for sample using maxappls 60

MAXFILOP = 64 2 - 9150

db2 => update db cfg for sample using maxappls 160

db2 => update db cfg for sample using AVG_APPLS 4

db2 =>update db cfg for sample using MAXFILOP 256

can see updated parameters from client

tcpip ..... not started up properly Check the DB2COMM variable if it it is set

db2set DB2COMM

How to terminate the database if processes are still attached:

db2 force applications all

db2stop

db2start

db2 connect to dbname (locally)

How to trace logs withing the db2diag.log file:

Connections to db fails:

Move the db2diag.log from the sqllib/db2dump directory to some other working directory ( mv db2diag.log
db2 update dbm cfg using diaglevel 4

db2stop

db2start

db2trc on -l 8000000 -e 10

db2 connect to dbname (locally)

db2trc dump 01876.trc

db2trc flw 01876.trc 01876.flw

db2trc fmt 01876.trc 01876.fmt

db2trc off

Import data from ascii file to database

db2 " import from inp.data of del insert into test"

db2 "load from '/cs/home/tech1/seela/inp.data' of del insert into seela.seela"

db2 < test.sql

Revoke permissions from the database from public:

db2 => create database GO3421

DB20000I The CREATE DATABASE command completed successfully.

Now I want to revoke connect, createtab bindadd on database from public

On server: db2 => revoke connect , createtab, bindadd on database from public

Now on client, as techstu, I tried to connect to go3421

db2 => connect to go3421

SQL1060N User "TECHSTU " does not have the CONNECT privilege. SQLSTATE=08004

Now I have to grant connect privilege to group ugrad

On server:

db2 => grant connect, createtab on database to group ugrad

DB20000I The SQL command completed successfully.

Tested on client I can connect successfully.

Now on the client, I can connect as a student, list tables but not select. I

can still describe tables

To prevent this:

On server

revoke select on table syscat.columns from public

Now on client, I cannot describe but also on my tables.

db2 => revoke select on table syscat.columns from public

DB20000I The SQL command completed successfully.

db2 => grant select on table syscat.columns to group ugrad


On server:

db2 => revoke select on table syscat.indexes from public

DB20000I The SQL command completed successfully.

select * from syscat.dbauth will display all the privileges for

dbadm authority:

DBADMAUTH CREATETABAUTH BINDADDAUTH CONNECTAUTH

NOFENCEAUTH IMPLSCHEMAAUTH LOAD AUTH

select

TABNAME,DELETEAUTH,INSERTAUTH,SELECTAUTH from

syscat.tabauth

grant connect, createtab

grant connect, createtab on database to user techstu

to group ugrad


Instance Level Authority

db2 get dbm cfg

db2 get admin cfg

db2 get db cfg

CLP using filename on the command line

Db2 -f filename.clp

The -f option directs the clp to accept input from file.

Db2 +c -v +t infile .. The option can be prefixed by a + sign or turned on by a letter with a -sign

+c is turned off, -v turned on and -f turned on

c is for commit, v for verbose and f for filename

-t termination character is set to semicolon

Getting db snapshot

Getting db snapshot
=====================
db2 "get snapshot for db on IBSDB"> dbsnapshot.out

Getting dbm snapshot
=====================
db2 "get snapshot for dbm" > dbmsnapshot.out

Getting dynamic SQL snapshot
===========================
db2 "get snapshot for dynamic sql on IBSDB" > dynamicsql.out

Getting application snapshot
============================
db2 "get snapshot for applications on IBSDB" > applications.out

Getting lock snapshot
====================
db2 "get snapshot for locks on IBSDB" > locks.out

Kill application that lock db
===============================
force application(lock_id)

db2pd -

A new DB2 UDB utility for monitoring DB2 instances and databases

ref

How can I make an SQL INSERT faster?

DB2 has added a new keyword to the CREATE and ALTER TABLE SQL statements: APPEND. This new keyword enables a pretty simple concept. If you CREATE or ALTER a TABLE to APPEND YES, DB2 simply sticks the new row at the end of the table*, makes no attempt at searching for available space, and makes no effort to preserve any kind of clustering order. Because you can ALTER this attribute on and off, you can switch it on (YES) for that massive insert batch job you run once a month and always follow with REORG/RUNSTATS anyway, then switch it back off (NO) for your day to day online insert processing. REORG is unaffected by the APPEND option so you can use it in conjunction with a tables clustering options allowing the object to take advantage of a faster insert and still maintaining a clustered sequence by the REORG and LOAD utilities.

The APPEND will work for all tables except those created in LOB, XML, and work files table spaces. BTW, this process is for insert and online LOAD operations. There is also a new column, APPEND, in SYSIBM.SYSTABLES so you can track when this feature has been turned on or off. In addition, you are going to see some index relief for inserts in DB2 9. But I'll save that for another post.



Ref

Revision: Runstats vs Reorg

In case you are new, we run REORG & then RUNSTAT when we do mass inserts/updates/deletes on DB or individual tables,so that the query runs faster . REORG and RUNSTAT are DB command.

RUNSTATS

1) Updates datadictionary stats. When a query is fired it reads the data dictionary table (dictionary managed) and calculates the cost,etc.

2) Gathers summary information about the characteristics of the data in table spaces and indexes. This information is recorded in the DB2 catalog, and is used by DB2 to select access paths to data during the bind process. It is available to the database administrator for evaluating database design, and determining when table spaces or indexes should be reorganized .

REORG

1) Put all the contiginous blocks together,so data reads / inserts will happen faster as it need not scan the pages. (example is defragmentation in windows)

2) Reorganized a table space to improve access performance and reclaim fragmented space. In addition, the utility can reorganize a single partition of either a partitioned index ora partitioned table space. If you specify REORG UNLOAD ONLY or REORG UNLOAD PAUSE, the REORG utility unloads data in a format acceptable to the LOAD utility of the same DB2 table space.

REORGCHK

do a reorgchk on a certain schema, which will runstat all tables in the schema, whereas doing runstats alone,
then you need to do it table by table.


TIPS

1) Run REORG & then RUNSTAT.

2) It is not necessary to run reorg for all tables :use REORGCHK ON TABLE ALL find which tables require reoganization and run REORG & then RUNSTAT for that specified tables.


EXAMPLE
REORG INDEXES ALL FOR TABLE MYSCHEMA.TABLEA ALLOW WRITE ACCESS;
RUNSTATS ON TABLE MYSCHEMA.TABLEA WITH DISTRIBUTION AND DETAILED INDEXES ALL;
RUNSTATS ON TABLE MYSCHEMA.TABLEA ON ALL COLUMNS ALLOW WRITE ACCESS;


ref

Where exactly is a DB2 plan stored?

Catalog contains information about plans in the following tables:

• SYSIBM.SYSDBRM
• SYSIBM.SYSPLAN
• SYSIBM.SYSPLANAUTH
• SYSIBM.SYSPLANDEP
• SYSIBM.SYSSTMT
  

And, the DB2 Catalog contains information about packages in the following tables:

• SYSIBM.SYSPACKAGE
• SYSIBM.SYSPACKAUTH
• SYSIBM.SYSPACKDEP
• SYSIBM.SYSPACKLIST
• SYSIBM.SYSPACKSTMT
• SYSIBM.SYSPKSYSTEM
• SYSIBM.SYSPLSYSTEM

ref

Step SQL Optimize

db2expln -d tibsdb -f xx/sql -o xx.log

db2expln - SQL Explain Command
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/core/r0005736.htm

The Two Biggest DB2 Performance Things
ref

Check index
====
db2 "describe indexes for table tibsadmin.fundtransferhistory"
db2 "SELECT colnames,tbname FROM SYSIBM.SYSINDEXES where upper(tbname) =upper('fundtransferhistory') ";

db2expln -d tibsdb -f 1.sql -o 1.log


drop index ;
CREATE INDEX {index_name} ON {table} ( {column1} ASC, {column2} ASC) ;
CREATE INDEX {index_name} ON {table} ( {column1} ASC) CLUSTER ;
**cluster index is physical index. Only 1 cluster index per table

Setup JMeter for load test
====================
pre requisite-put the db2 drirver.jar to jmeter/lib

1)Test Plan -> Add Thread Group
2)Thread Group->Add->Config Element ->JDBC Connection Configuration
3)Max number of connection = {> Number of thread}
Database url = jdbc:db2://{host}:{port}/{dbname}
Jdbc driver classess = com.ibm.db2.jcc.DB2Driver
username=
password=
**package for "com.ibm.db2.jcc" is ibm jdbc driver type4
**package for "COM.ibm.db2.jdbc" is ibm jdbc driver type2 which use by websphere server
**"DB2Driver" class is normal driver
**"DB2ConnectionPoolDataSource" class is connection pool data source.
****jdbc:db2://10.100.101.30:61099/tibsdb


4)Thread Group->Add->Sampler->JDBC Request
**For insert sql, u can create Non Duplicate ID using this function
CHAR( ${__counter(FALSE,100)} )

example:
INSERT INTO Schema.Mytable VALUES ( CHAR( ${__counter(FALSE,100)} )
, 'AbcValue')

5)ThreadGroup ->Add->Listner
i)Summary report - to view summary statistic
ii)View Results Tree - to check the request and response data
iii) Aggregate Report - Have 90% line











ref 1 : How to easily populate a table with random data

Check index db2

db2 "describe indexes for table tibsadmin.fundtrasnferhistory"

SELECT colnames,tbname FROM SYSIBM.SYSINDEXES
where tbname ='ECEREMITTANCE';

catalog node and catalog db @ db2

CATALOG [ADMIN] TCPIP NODE node-name REMOTE hostname [SERVER service-name]
db2 => catalog tcpip node rhbdemo remote 192.168.1.23 server 50000

db2 => list node directory

CATALOG DATABASE database-name [AS alias] [ON drive | AT NODE node-name]
db2 => catalog database ibsdb as rhbdb at node rhbdemo

ADMIN_CMD procedure

The ADMIN_CMD procedure is used by applications to run DB2 command 9 line processor (CLP) administrative commands using the SQL CALL 9 statement. 9 The procedure currently supports the following CLP commands:

use admin_cmd('')

ref

note. This only for build in for version 8.2 FixPak 4 (equivalent to version 8.1 FixPak 11) and DB2 Data Warehouse Edition Version 9.1.

Db2 version and Fixed pack level

1) run
db2support . -s

2) run
db2level

ref

Do u know tat we can query sql error code in db2 command line processor?

db2 => CALL runstats('unknown.table')@
SQL0443N Routine "STOLZE.RUNSTATS" (specific name "RUNSTATS") has returned an
error SQLSTATE with diagnostic text "Error -2306 returned by db2Runstats.".
SQLSTATE=38RS1

db2 => ? sql2306@

SQL2306N The table or index "" does not exist.

ref

Db2 import/export data

db2 import from datafile1.del of del replace into table1

IMPORT FROM "C:\aaaaaaaaa" OF IXF commitcount 10000 MESSAGES "C:\zzzzzz" INSERT INTO IBSADMIN.ARC_SETUP;
EXPORT TO "C:\aaaaaaaaa" OF IXF MESSAGES "C:\zzzzzz" SELECT * FROM IBSADMIN.ARC_SETUP;

import from date of DEL commitcount 10000 insert into mytable
EXPORT TO "C:\x" OF DEL MESSAGES "y" SELECT * FROM IBSADMIN.ARC_TABLE;

db2 export to mbuser.csv of del "select * from tibsadmin.mbuser"
db2 import
from mbuser.del of del "insert_update into tibsadmin.mbuser"

import

export

EXPORT in DEL forma get SQL3100W in DB2

I am using UDB V8.2 on AIX . When I do an EXPORT in DEL format , it gives a
SQL3100W on certain rows.

mainly, This warning occurs on rows that do not have any columns that are
over 254 chars.

In Command ref on page 365 second paragraph it says that if the char col is greater then 254 and using DEL format only you will get an error. IXF stores data diferently then DEL does, thus not effected by this limitation.

ref

How to list tables in another schema?

db2 > List tables for schema s1
db2> list tables for schema s2
db2> list tables for schema s3

or to list for all schemas

db2> list tables for all

or

db2 > Select TABSCHEMA, TABNAME, DEFINER from SYSCAT.TABLES where tabschema
IN ('S1','S2','S3')
tabschema is the schema name & Definer is the user name

EXTRA
to check store procedure
db2 > select PROCSCHEMA, PROCNAME from SYSCAT.PROCEDURES where procname = 'RPT_REGUSER_LOGIN_I' or procschema = 'IBSADMIN';

ref

Cursor closes at COMMIT unless you use the WITH HOLD option on cursor.

Cursor closes at COMMIT unless you use the WITH HOLD option on cursor.

Example
OPEN UPDATE_CURSOR WITH HOLD;

-- Declare cursor
FOR vl AS
c1 CURSOR with hold FOR
 SELECT customer_id as id,full_name from customer where full_name like 'test%'
DO
   update customer set status ='A' where customer_id = id;
   COMMIT;   <======= Can commit inside Loop if you choose WITH HOLD 

END FOR;
COMMIT;

ref ,
ibm

alter table in stored procedure

CREATE PROCEDURE ppg_gc (IN expiryTime INTEGER, IN batchSize INTEGER)
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN


declare v_stmt varchar(20);
--now store ur query inside this variable,then prepare the statement.

v_stmt = 'alter table emp drop primary key';
prepare S1 from v_stmt;
EXECUTE IMMEDIATE S1;

END @


ref

Doing LOOP in Stored Procedure

Code

DECLARE v_start int default 0;--Start month
DECLARE v_end int default 12;--End month

set v_start = 3;
set v_start = 6;
--[BEGIN] LOOP
L1: loop

if (v_start > v_end  ) then leave L1;
end if;
set v_start = v_start+1; 
--DO SOMETHING 
END FOR;
--[END] LOOP

Is there an equivalent DB2 syntax for the Oracle DECODE function?

QUESTION POSED ON:
Is there an equivalent DB2 syntax for the Oracle DECODE function?

EXPERT RESPONSE

Well, first of all, let's explain the Oracle DECODE expression for those not familiar with Oracle. A DECODE expression will look like this:

DECODE(expr,search,result,default)

There can be multiple search values and results, and default is optional. To evaluate this expression, Oracle compares expr to each search value one by one. If expr is equal to a search, Oracle returns the corresponding result. If no match is found, Oracle returns default, or, if default is omitted, null is returned. If expr and search contain character data, Oracle compares them using nonpadded comparison semantics. The maximum number of components in the DECODE expression, including expr, searches, results, and default is 255.

So, basically, DECODE changes the value of an expression if the expression is equal to one of the values in the searched list. For example, this expression decodes the value deptno. If deptno is 10, the expression evaluates to 'ACCOUNTING'; if deptno is 20, it evaluates to 'RESEARCH'; etc. If deptno is not 10, 20, 30, or 40, the expression returns 'NONE'.

DECODE (deptno,10, 'ACCOUNTING',
20, 'RESEARCH',
30, 'SALES',
40, 'OPERATION',
'NONE')

In DB2 this can be accomplished using CASE expression. To write the equivalent of the above using DB2 you can write the following SQL statement:

SELECT CASE deptno
WHEN 10 THEN 'ACCOUNTING'
WHEN 20 THEN 'RESEARCH'
WHEN 30 THEN 'SALES'
WHEN 40 THEN 'OPERATIONS'
ELSE 'NONE'
END CASE
FROM EMP;

ref1,ref2