In order to create a DB2 stored procedures from the command line, all what you need to do is:
"@" at the end of your
"CREATE PROCEDURE" script as follows:
CREATE PROCEDURE myScheme.myProcedure
IN someParameter BIGINT
-- Some SQL statements
2. Let’s assume that the script file name which contains the stored procedure sql is
"someScript.sql", In order to execute the DB2 stored procedure creation script, run the following command from the command line:
db2 [email protected] -svf someScript.sql
After running the command, you should see the success message as follows:
"DB20000I The SQL command completed successfully.
Sometimes, you may need to get the current stamp of the database server if you need for example to calculate the difference between the entry creation time and the current time.
In order to get the current database server timestamp in DB2, you can get it by querying the
sysdummy1 table in the
sysibm schema as follows:
SELECT CURRENT TIMESTAMP AS CURRENT_SERVER_TIME FROM sysibm.sysdummy1
You may want to get the db2text indexes information in your current DB2 database. In order to do this, you can get these information using this SQL statement:
SELECT INDNAME, TABNAME, COLNAME, LANGUAGE, RECREATEONUPDATE, CREATIONTIME, UPDATETIME,
UPDATEFREQUENCY FROM db2ext.textindexes
A very common requirement is to have pagination on the level of the database especially if your query result returns thousands of database records. This post shows you how to make database pagination in DB2.
In order to make a database pagination in DB2, you need to use the ROW_NUMBER() function as follows.
(SELECT column1, column2, column3,
ROW_NUMBER() OVER (ORDER BY column1) AS RN
FROM TABLE(OR VIEW)
WHERE column1=xyz1 and column2=xyz2)
SELECT column1, column2, column3 FROM
CVIEW WHERE RN BETWEEN X AND Y
ORDER BY RN
You can use this template query if you want to retrieve records from
Y from the
TABLE(OR VIEW). The template query shows you how to retrieve
column3 from the
TABLE(OR VIEW) with the specified condition
column1=xyz1 and column2=xyz2 ordered by
This is all about.
You can execute a SQL file in a DB2 database simply using the following command in the DB2 CLP (Command Line Processor), Let’s assume that the file name you want to run is
db2 -tvf myfile.sql
Before you execute this command, you need to connect to the database which you will apply the script to using:
db2 connect to yourDatabase
You may face this issue when you try to execute a command on the Linux shell. I faced this issue when trying to execute the following DB2 command on the server Linux server using the putty shell:
db2text create index someindex for text on someTable(someField) CONNECT TO someDB;
In order to fix this issue, I have to set
\ before every
( as shown below:
db2text create index someindex for text on someTable\(someField\) CONNECT TO someDB;
Another solution is to add double quotes on the command as follows:
db2text "create index someindex for text on someTable(someField) CONNECT TO someDB";
In DB2, you may face this error. This error means that the access to the table is restricted. Do not be worry if you see this error because it may happen after the
ALTER TABLE DROP COLUMN statement.
In order to fix this error, you need to run the
REORG command as follows:
REORG TABLE [YOUR_TABLE_NAME]
After running this command, you will be able to access the table.
For more information about the DB2
REORG command check:
Sometimes, you need to run the DB2
REORG command from the JDBC code. You will need to run the
REORG command usually after executing the
ALTER TABLE DROP COLUMN statement. In order to execute the
REORG command from JDBC, you can do this as follows:
Call Sysproc.admin_cmd ('reorg Table [YOUR_TABLE_NAME]');
I hope that this tip can be useful to you.