You are browsing the archive for Database.

Creating DB2 Stored Procedure from the command line

October 2, 2012 in Database

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

September 25, 2012 in Database

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

Getting the db2text indexes information in DB2 9.x

September 16, 2012 in Database

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

DB2 pagination for large data volumes

September 3, 2012 in Database

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.

Executing a SQL file in DB2 database

August 4, 2012 in Database

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 "myfile.sql":

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

syntax error near unexpected token `(‘

August 4, 2012 in Database, Linux

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

May 17, 2012 in Database

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

May 15, 2012 in Database

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.

Skip to toolbar