Creating DB2 Stored Procedure from the command line

In order to create a DB2 stored procedures from the command line, all what you need to do is:
1. Place "@" at the end of your "CREATE PROCEDURE" script as follows:

CREATE PROCEDURE myScheme.myProcedure
(
IN someParameter BIGINT
)
LANGUAGE SQL 
BEGIN
-- Some SQL statements

END@

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 -td@ -svf someScript.sql

After running the command, you should see the success message as follows:

"DB20000I  The SQL command completed successfully.

DB2 Getting the current database server timestamp

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

DB2 pagination for large data volumes

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.

WITH CVIEW 
(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 X to Y from the TABLE(OR VIEW). The template query shows you how to retrieve column1, column2, and column3 from the TABLE(OR VIEW) with the specified condition column1=xyz1 and column2=xyz2 ordered by column1.

This is all about.

syntax error near unexpected token `(‘

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";

Fix of reason code “7” SQLCODE=-668 SQLSTATE=57016

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:
http://publib.boulder.ibm.com/infocenter/db2e/v9r1/index.jsp?topic=%2Fcom.ibm.db2e.doc%2Fsqlreorgt.html

DB2 calling REORG command from JDBC

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.