You can monitor dynamic SQL statements by using SYSIBMADM.TOP_DYNAMIC_SQL view but you need an event monitor to monitor SQL statements inside a DB2 Stored Procedures as those statemets are static in nature. Let us understand this through a simple exercise: Step-A:...read more
The default port to use Optim Performance Manager for web console is 55000 and 55001 for SSL. This requires opening up 55000 through firewall and what if you want to use port 80 on the OPM Server. It is little tricky since OPM Web Console runs as non-root user and...read more
Equivalent of Oracle KEEP can be in DB2 using a nested query OVER (PARTITION BY ..) and then using MAX and MIN on top of it without having any extra sort. It is best explained by an example: Case-1: Oracle query using KEEP function: CREATE TABLE emp(id INTEGER, name...read more
Steve Rees and Hania El Ayoubi from Toronto Lab have shared this utility function written in SQL PL to browse table data in column value pair. It becomes useful when browsing wide table data. For example, if one looks at the table sysibmadm.mon_db_summary or table...read more
If a table is created with an IDENTITY column and you need to make sure that your identity column starts from the last max value that you load the data. IBM Data Movement Tool does this automatically but if you are not using the tool and want a way to sync up start...read more
Packaging for DB2 client has changed. Review this article for detailed information and how to download thin / thick clientsread more
Read Problem determination guide hereread more
If you are using DB2 Control Center on Windows and wondering how to set the code page of your choice, follow these steps. Open a DB2 command window: C:\>SET DB2CODEPAGE=1208 C:\>db2cc The first command sets the environment variable DB2CODEPAGE to 1208 (UTF-8) and...read more
Steps to configure Oracle database federation from DB2 on zLinux. (Or any kind of *nix) These steps are for Oracle database (any supported version from Oracle) to DB2 9.5 but they should work as it is for DB2 9.7 or later with proper Oracle client install. 1. Install...read more
Steps to configure SQL Server federation from zLinux. (Or any kind of *nix) These steps are for DB2 9.5 but they should work as it is for DB2 9.7 or later with proper install of IBM branded ODBC drivers. 1. Install DB2 2. Install IBM Branded DataDirect ODBC Drivers....read more
When you want SP to return message_text and you also want to handle error code as per your choice, follow this simple example. CREATE PROCEDURE myproc( OUT v_msg VARCHAR(300)) LANGUAGE SQL BEGIN DECLARE V_ERROR INTEGER DEFAULT 0; DECLARE CONTINUE HANDLER FOR...read more
This is by no means an exhaustive list of new features in DB2 9.7. But, it is the list of features that are most important to me as a DBA or a developer. Online Schema Changes See Burt's article on Online Schema changes. CGTT - Created Global Temporary Table Now, it...read more
Starting version DB2 9.7, you can use following query to detect unused indexes in DB2. SELECT INDSCHEMA, INDNAME, TABNAME FROM SYSCAT.INDEXES WHERE LASTUSED = '01/01/0001'; The column LASTUSED tells when that index was last accessed in DB2. This is not per se a...read more
I am going to show you an easy way to do online schema changes in DB2. The best to learn things is by doing some simple examples. My intent is to make you an expert in 1/2 hour if you learn the following concepts. Create a database with and without automatic storage...read more
Finally DB2 implemented a long standing demand for setting the Java application name so that you could see the name through DB2 utilities instead of seeing db2jcc_applica. You set a connection property clientProgramName to your chosen name and the db2 utilities will...read more
There are DB2 LUW configuration parameters that can be set to AUTOMATIC and managed by the DB2 automatic memory manager (STMM). One advantage of this is that DB2 will automatically adjust the memory it uses to get the work done based on the characteristics of the...read more
Use this DB2 Stored Procedure to build an INSERT statement. This is very useful when you are developing some Java or .Net program or any other program and need an easy way to do this. Thanks to Serge Rielau of DB2 Toronto lab for sharing this with the DB2 community....read more
Thanks to Serge Rielau for insight in DB2 MERGE. For example, if you have this Oracle UPDATE statement and wondering how can you use this in DB2. Here is the solution. Oracle UPDATE UPDATE ( SELECT clm.c2 wc1, m.c2 wc2 FROM schema1.table1 m, schema1.table2 clm WHERE...read more
If you have DB2 scripts that you want to run through your Java programs, you normally use Runtime.getRuntime().exec method to run system commands. The following sample code tells how to run DB2 scripts. The following code shows it for both Windows and Unix systems....read more
Are you looking for Matt Devlin's Perl Scripts for DB2? Even though, they are old but still they are relevant with new release of DB2 particularly if you know how to modify them. Matt Devlin's website http://mattdevlin.net is no longer available but you can get...read more
If you use MTK (Migration Toolkit from IBM) to migrate your database to DB2 on Windows platform, the migration goes smooth but you may see problem when you FTP data file and LOAD script to Unix platform if you do not do it right. For example, you chose ASC (ASCII)...read more
Mark Mulligan – (firstname.lastname@example.org) DB2 DBA 29 November 2008 Download Scripts Introduction Have you ever run the db2 "call get_dbsize_info(?,?,?,0)" command to get the size of your DB2 LUW database and noticed that the size is much smaller than the space...read more
Mark Mulligan – (email@example.com) DB2 DBA 16 November 2008 Introduction If you have application jobs or users that run the DB2 load utility and would like to keep track of this information over time there is valuable information stored in the DB2...read more
Thanks to Max Petrenko of DB2 Toronto Lab for sharing a very useful script to remove check pending status from the DB2 tables after LOAD or other operations. It is easy to generate a check pending script, but the importance of this script is that it builds the...read more
Mark Mulligan – (firstname.lastname@example.org) DB2 DBA 01 November 2008 A critical part of any database disaster recovery plan is to make sure that the environment surrounding the database is the way it was before the disaster in order to provide the same level of...read more
Say, you want to concatenate all rows of a column and show it as a single row in the same fashion aggregate function works. $ db2 "create table t1(line# int not null, word varchar(20))" $ db2 "insert into t1 values(1,'The'),(1,'horse'),(1,'is'),(1,'white')" $ db2...read more
Edin I. Aganagic shares his tips on how to increase incremental refresh/maintenance performance of MQTs in DB2. Create an index on the GROUP BY columns. It helps speed up MQT look-ups needed to decide whether existing row needs to be changed, new one inserted, or last...read more
Download DB2 Logger from here Make sure that you read Readme.txt file before you compile the tool.read more
You can always turn off global logging by setting global_logging to N. However, if you want to shave every millisecond from your stored procedure's execution time, comment out all CALL DB2. statements when you are sure that your SQL-PL or external stored procedures...read more
6.15 When I try to free the shared memory using the ipcrm, I get an error stating that I am not allowed.
The logger stored procedures run under the DB2 fenced user. Log in as the DB2 fenced user and try again to remove it. If the DB2 fenced user is nobody, ask your system administrator to free the shared memory using the ipcrm command.read more
Yes. This is an open source stored procedure logger. Please retain the IBM Copyright message in each source file.read more
Did you copy the bldrtn and bldapp files from ~/sqllib/samples/c to your directory? Try again and if you still get error, please send me a note and tell me what did you do to fix the errors.read more
6.12 I am getting compiler errors when I try to compile the framework on the Solaris platform. What do I do?
I have not tested this framework on the Solaris platform. Please let me know the errors and I will try to fix them.read more
6.11 I am trying to compile this framework on the Windows® platform and I am getting lot of compiler errors. What should I do?
This logging framework works on Linux/Unix platforms only at this moment. I am looking for help who can port or rewrite this on Windows. If you are interested, please send me mail at email@example.com more
6.10 The logging to the file has stopped. I do not see any more messages showing up in the log file. What happened?
Verify that the file system containing the splog directory is not full.read more
The logging framework is designed to debug the stored procedures, and the hardw method is used to debug these logging routines.read more
This is the most common issue. Look for your log file in the /tmp directory. This is related to the fenced user ID and file permissions on the ~/sqllib/splog directory. Ask your DBA to create a splog directory in ~/sqllib and grant full permissions on this splog...read more
6.07 In the log file, I see a number on the line “Begin Stored Procedure *** ”. What is this number?
This is the shared memory location used by the splogger library. When you stop DB2, it will free up shared memory used by DB2. For the splogger shared library, there is no way to determine when to free up this shared memory. You can use this number to free up the...read more
Set the DB2 instance-level parameter KEEPFENCED=YES using the CLP command UPDATE DBM CFG USING KEEPFENCED YES. After restarting the instance, the external stored procedures library will be loaded in memory by DB2 on first invocation of the stored procedure. These...read more
This is a normal result from spcat. It uses the DROP PROCEDURE command on the DB2 logging stored procedures, and if they do not yet exist in the database, you will see the error message above. The next time you run spcat, you will not get the error.read more
The bld script is used to compile splogger as a stand-alone program instead of a shared library, for testing. It makes a splog binary file, which you can use to execute the main program in splogger.sqc.read more
Modify the makefile and specify values for ALIAS, UID, and PWD. Put a CONNECT statement in spcat and spalter.read more
There is no connect statement in spcat, as it assumes the implicit connection to the database. How to use the implicit connection? $ db2set DB2DBDFT= $ db2stop force $ db2start $ db2 activate...read more
The file permissions on bldrtn might have changed, depending on how you copied the files to your system. Try changing the file permissions for bldrtn, embprep, and spcat using the chmod command. For example: $ chmod +x embprep bldrtn spcat bld spalterread more
Here is a list of common questions and answers that people have asked me while learning or maintaining the framework.read more
The logging framework routines are written in C, so you can use them directly from your C stored procedures without having to make a call to the logging stored procedures. The example C stored procedure below uses the logging API. To use the logging methods, include...read more
After you run make successfully, you will notice a file generated known as testsp.sql. The only reason we generate this file instead of giving this file as it is is to calculate the size of logging handle properly for your Linux/Unix platforms. This is due to the fact...read more
In order for you to set up the logging framework, you might need help from your system and/or database administrator to set up the stored procedure logging directory. Before we delve into this, let us understand how external stored procedures (Our logging framework...read more
An explanation of logging tokens The first DB2.OPEN_LOG call builds a linked list hash table as shown below from the logging tokens kept in the shared memory. For each logging token read from the shared memory or configuration file on its first invocation, OPEN_LOG...read more
Motivation You might need to write informational, error, and debug messages to a log file for a number of reasons: To test, analyze and validate business logic. To track and review detailed error messages. To fix business logic bugs encountered in production...read more
I published an article Log stored procedures messages for DB2 on Linux or Unix on IBM Developerworks website in January 2006. Since then, I have made several bug fixes and enhancements to this logging framework. The enhanced logging framework is documented here with...read more
If you are migrating your ODBC related code from one database to DB2, you might run into an issue of single quote vs. double quote issue. For example: Your ODBC code might issue a call to a Stored Procedure using double quote as shown below: CALL...read more
Sometimes, it may be necessary to expose data in your flat file with the relational data. There might be some situations where there is no option to load that data in a table. But still, you need a way to use this flat file data in your SQL statements. DB2 provides...read more
DB2 provides a way to calculate date from a Julian day. For example: 2008075 should match to the 75th day of 2008 and i.e. 2008-03-15. If you try to do, $ db2 values date(2008075) You will get a date of 12/03/5498 but that is what you do not want. The argument to DATE...read more
Step-1 : Create Explain tables See this entry http://www.db2ude.com/?q=node/65 for how to create explain tables in DB2. Please note: You need to create explain tables in the USER-ID of the logged-in user. If you created explain plans in the schema name of some userid,...read more
Improve concurrency with DB2 9.5 optimistic locking New optimistic locking feature to avoid maintaining long-lived locks IBM® DB2®, Version 9.5 for Linux®, UNIX®, and Windows® provides enhanced optimistic locking support, a technique for SQL...read more
Self-tuning memory was first introduced in IBM ® DB2 ® 9.1 and simplifies the task of memory configuration by automatically setting optimal values for most memory configuration parameters, including buffer pools, package cache, locking memory, sort heap, and total...read more
Introduction This article discusses DB2 LUW Deep Compression from the perspective of someone who planned, organized, communicated and coordinated the implementation of this in development, test, regression test and production environments for multi-terabyte data...read more
You have installed DB2 on your database server and now you need to setup your IBM PHP client to connect to DB2. Do you require a full ~500 MB client install? Mostly, system administrator or DBAs grind their teeth when they find out that for a simple connect from PHP,...read more
You are trying to figure out the best approach to do the case in-sensitive search in DB2. You must first read these 3 excellent articles written on this topic by 3 IBMers. Blair Adamache Doug Doole Knut Stolze In a nutshell, you can take one of the approach as...read more
You are new to ibm_db2 or db2 and PHP driver available with thin ODBC-CLI client do not match with your version of PHP. The following steps take drudgery out of complex compilation process for those DBAs or system administrator who are averse to compilation process....read more
Yes, db2 has a top like utility called db2top written by 2 IBMers Subho Chatterjee and Jacques Milman. It uses DB2 snapshot APIs to extract information from DB2 engine and shows in a format that you are familiar with top utility on Unix systems. Click at...read more
Use following SQL to find out the ISOLATION LEVEL used by DB2 packages. SELECT 'SHOW_RSETS' "STORED PROCEDURE", SUBSTR(PKGNAME,1,18) PACKAGE, "ISOLATION" FROM syscat.packages WHERE pkgname = (SELECT deps.bname PACKAGE FROM sysibm.sysdependencies deps,...read more
If you have been using UNIQUE_IDENTIFIER as a data type and use newguid as a default in your SQL Server table definition, you can use a UDF called from a trigger to simulate same behavior in DB2. This work around is due to the fact that DB2 does not yet support...read more
There are several ways one can do DB2 JDBC tracing and it all really depends how creative you are. Check version of JCC Driver $ java com.ibm.db2.jcc.DB2Jcc -version Simple nonsense solution to use JCC Tracing import java.sql.Connection; import java.sql.DriverManager;...read more
You can put this script as a cron job so that you get DB2 severe messages through email or to your pager. #!/bin/bash # To display severe errors logged for the last 1 day: db2diag -gi "level=severe" -H 1d | /bin/mail -s "DB2DIAG severe messages in last 24 hours"...read more
In Oracle world, DBAs generally say that Oracle can do static and dynamic SQL. When they say this, it amuses a DB2 DBA since Oracle way of static and dynamic SQL as perceived by an Oracle DBA is no way near to what is meant by static and dynamic SQL in DB2. In Oracle,...read more
This is a common practice to bump the size of all buffer pools in DB2 as that gives the maximum performance boost. It is true but as a DBA you will like to monitor the buffer pool hit ratio and use of the buffer pool to make sure that you are not wasting your memory....read more
As a DBA, your efforts is to make sure that you have as much asynchronous read activity on your database. The intentions are good but how do I know the amount of asynchronous activity going one in my database. This SQL will give you the Asynchronous read ratio (ARR)...read more
DB2 snapshot command or use of the snapshot administration views or SQL table snapshot functions require latches and use of engine resources to return the information but db2pd tool does not require those latches etc to return the information. You should use db2pd...read more
Sometime back, I wrote about how to know about a fenced user id in DB2 at http://www.db2ude.com/?q=node/30. The other day, someone asked me how do I change the fenced user id in DB2? This is very easy but will require an outage if you are already in production. I...read more
Did you wonder why your isolation level escalated to CS from UR? For this discussion, you might want to read about unambiguous cursor at DB2 at http://db2ude.com/?q=node/41. So even if you open a cursor with UR isolation level, there is a possibility that this might...read more
Did you know about P, B or L locks in DB2? They are internal locks in DB2. As a DBA, it is good to know about them so that we know how to deal with them or avoid them. P Locks These are package level locks and you will see them when any application is executing a...read more
You can use following query to find out the name of the bufferpool associated with a table space. SELECT SUBSTR(BPNAME,1,18) "BUFFER POOL", SUBSTR(TBSPACE,1,18) "TABLE SPACE", B.PAGESIZE FROM SYSCAT.BUFFERPOOLS B, SYSIBM.SYSTABLESPACES T WHERE B.BUFFERPOOLID =...read more
Most of the GUI tools create EXPLAIN tables automatically for you but if you need to create them, you can use one of the following approach. DB2 provides EXPLAIN.DDL in ~/sqllib/MISC directory and you can execute this file $ db2 connect to sample $ db2 -tf...read more
You are a DBA and you want your users to use an isolation level of UR who are doing adhoc queries or typing SQLs using DB2 CLP or some form of the applications. By using DB2 CLP Use SET CURRENT ISOLATION LEVEL command db2 connect to sample db2 set current isolation...read more
Through a application server where you open dedicated connections to database, you get information about your SQL using applications server user-id only. How do you tie those information back to the end user who initiated the transaction? One option is to use DB2...read more
LAST_DAY(SYSDATE) ==> values (current date + 1 month) - day(current date +1 month) days FIRST_DAY(SYSDATE) ==> values (current date - day(current date + 1 month) days) + 1 DAYread more
How do you get index space usage by a table? DB2 provides a Table function ADMIN_GET_TAB_INFO to retrieve information about a table. For example - To find index space used by a non-partition table's indexes, you can use following SQL: SELECT index_object_p_size as...read more
Generally people try to tune DB2 backup by manipulating PARALLELISM parameter but there is no need to manipulate this parameter if you are looking for a least possible time to backup the database. Let DB2 figure out the optimal value for you for PARALLELISM. But, you...read more
If you are using LOAD utility to load 100s of tables, you can use following SQL to find out the tables which are in LOAD pending status. SELECT TABSCHEMA, TABNAME, LOAD_STATUS FROM SYSIBMADM.ADMINTABINFO WHERE LOAD_STATUS IS NOT NULLread more
The new version of DB2 9.5 has enhanced DB2 audit facility significantly and now it makes a perfect sense for the DBAs to start using it. The traditional notion on any audit (DB2 or Oracle) is that it is very expensive. That is not the case now as you have the options...read more
You have setup your databases in HADR operation and you are wondering how my DB2 LOAD will transfer from one machine to another since DB2 LOAD operation is not logged. Well, the DB2 LOAD operation is of course logged but not the actual contents. So, how do I get my...read more
If you have enabled HADR between 2 databases, the following value of parameters might give you better performance. Database configuration parameters LOGFILSIZ = 6000 LOGBUFSZ = 512 SOFTMAX = 125 DB2 Registry variables db2set DB2TCP_CLIENT_RCVTIMEOUT=20 db2set...read more
DB2 9.5 deletes previous backup images, log files and copies of load images automatically. This alleviates lots of pain for maintaining DB2 backups and log files. Prior to DB2 9.5, these tasks were usually attained by the operating systems scripts. For example, Taking...read more
On windows platform when you create a DB2 database as per following syntax, it gets created in C:\DB2\NODE0000 location where DB2 is the name of the instance. C:\>db2 "CREATE DB TESTDB AUTOMATIC STORAGE YES ON C: USING CODESET UTF-8 TERRITORY US" What if, you want to...read more
DBAs have to comply with the licensing requirements for DB2. As a DBA, how do you know if you are using a particular feature of DB2 or not? I wrote about this long time back at here. In addition to above, you can also use this SYSIBMADM view to find out if you are...read more
Lots of you use DB2 on SAN storages either from IBM or EMC or other third party. Most of SAN storages have their own caching which is to enhance the performance. After working with many DB2 users, I have found that these file system caching is not used properly to use...read more
The db2batch utility is a powerful utility to time the SQL queries. You should be careful in using the db2batch utility during peak time in your production system just for one issue - db2batch utility uses RR isolation level. For long running SQL statements, the RR...read more
If you want to convert a 2 digit hex code to a single char code, here is the function that you can use. DROP FUNCTION HEX2CHAR; CREATE FUNCTION HEX2CHAR (hexcode varchar(2)) RETURNS CHAR(1) SPECIFIC hex2char LANGUAGE SQL CONTAINS SQL EXTERNAL ACTION DETERMINISTIC...read more
You might come across a need to simulate collation sequence of DB2 on mainframe (or MVS or z/OS) as it is on DB2 LUW (Linux, Unix and Windows) platform if you want your application to show the order of the data exactly same way as it is on the mainframe. The main...read more
If you are connected to Linux via SSH and you want to bump up number of max file open either for DB2 or for your any program, you might run into issues where it does not work for you. Say for example: You login as root and do this modification to...read more
Follow these steps if you want to create MTK Java UDFs manually. 1. Locate mtkoradrop.udf in your MTK directory and drop MTK UDFs $ db2 connect to sample $ db2 -td\! -f mtkoradrop.udf 2. Remove Jar if it was installed already. You can look at the ~/sqllib/function/jar...read more
When DB2 agent (db2bp process) makes a connection for your CONNECT request, it stores the IP address of the client in the application ID as shown below: $ db2 list applications Auth Id Application Appl. Application Id DB # of Name Handle Name Agents --------...read more
How do I know what is installed in DB2? Is there a way, I can determine about hidden components installed in DB2? How do I know which language packs are installed in DB2? Sometime, you might want to know answers about above questions. Normally, you will run db2level...read more
If you are converting from Sybase, you might run into issues for converting INT to HEX. In Sybase: 1> select inttohex(234) 2> go -------- 000000EA (1 row affected) In DB2: DB2 => values hex(234) 1 -------- EA000000 If you want the same exact behavior from DB2, you...read more
Did you get this DB2 SQL1035N error if you were trying to take offline backup? The error is obvious as someone is connected to the database and you cannot take offline database backup.
There are several solution that you can take to do offline backup when you get SQL -1035 error. They are described as below:
Method – Brute Force
C:\>db2_kill C:\>db2start C:\>db2 backup database sample to C:\db2backup
The above method is a brute force method and is not recommended. You may get unexpected results at the expense of even corrupting the database. It is not guaranteed that above will work always. You may still get SQL1035N error even after db2_kill. You try to now kill every process with “kill -9” command and still it is not guaranteed to work. It is not at all recommended to use this brute force method.
Method – Mild Force
C:\>db2 force applications all C:\>db2stop force C:\>db2start C:\>db2 backup database sample to C:\db2backup
The above method is less brute force but you may not be able to use this if there are other databases in your instance. Stopping instance to just take a database backup is not acceptable if there are other databases in the instance.
Method – Correct way
Scenario – Application server has retry logic
Let us say, you want to take offline backup of your database and there is an application server which is designed to connect to the database as soon as it becomes available. The application server has retry logic and it will keep on trying making a connection to a database but your intent is to take an offline database backup. You are stuck as you have to ask your application server administrator to shut down the application server so that you can take up the backup. This may not be sometime possible to get your application server down. The above brute force methods also do not work in this circumstance. The following is the correct way to take offline backup of the database under this scenerio.read more
Did somebody create a DB2 database for you and you are just getting started to work on inherited legacy? How do you find if the database was created with automatic storage YES or NO?
Let us do a simple exercise.
My laptop has two drives C: and D: and I have DB2 9 installed.
C:\>db2 create database sample2 on C:,D: C:\>db2 connect to sample2 C:\>db2 list active databases
Output on my machine was:
Active Databases Database name = SAMPLE2 Applications connected currently = 1 Database path = C:\DB2\NODE0000\SQL00004\
Now go to your windows explorer and check following folders:
In Sample2 directory on C: and D:, you have table spaces created on 2 storage paths that you specified when creating SAMPLE2 database.
Check folder C:\DB2\NODE0000\SQL00004\ and this is where your database was created. How do you know how SQL token SQL0004 is linked to the SAMPLE2 database? You got this from the output of LIST ACTIVE DATABASES command.
How do I know about storage paths if some one else had created the database for me? This simple exercise should help you.
Method – A
C:\>db2 connect to sample2
C:\>db2 update monitor switches using bufferpool on lock on sort on statement on table on timestamp on uow on
With above command, I turned on monitor switches for this current DB2 session. I discuss this elsewhere to explain it in more detail. It is not necessary to turn on all switches and above was just an example.
C:\>db2 get snapshot for all on sample2
You will see the following as a part of your snapshot output.
Number of automatic storage paths = 2read more
Automatic storage path = c:
File system ID = 2149054632
Storage path free space (bytes) = 50386874368
File system used space (bytes) = 49438912512
Did you ever wonder what is cursor blocking or row blocking in DB2?
Cursor or Row blocking in DB2 is a way to fetch number of rows in a single operation. This can improve performance of the applications.
There are 3 types of blocking which can be used for your cursors.
- ALL – Cursors specified with READ ONLY and FOR UPDATE OF clause. Blocking of rows will occur.
- NO – Blocking of rows will not occur
- UNAMBIGIOUS – Cursors specified with READ ONLY clause. Blocking of rows will occur.
You must have heard the term AMBIGUOUS CURSOR. What is it? An ambiguous cursor is the one where DB2 does not know if you want to do updates or not and hence it assumes that you are going to do the updates. DB2 treats ambiguous cursors as updatable.
Say for an example, this line of code
DECLARE c1 CURSOR FOR SELECT * FROM EMPLOYEE;
will open an ambiguous cursor and db2 assumes that you will update the rows. But if you say
DECLARE c1 CURSOR FOR SELECT * FROM EMPLOYEE FOR READ ONLY;
DB2 will open an unambiguous cursor for you.
The READ ONLY cursors are unambiguous as a rule of thumb. You may not say explicitly to DB2 to open a cursor as READ ONLY but DB2 may open a cursor as READ ONLY (unambiguous cursor) based upon your query if you are using SORT using full table scan etc.
The locks will be obtained for ambiguous cursors based upon isolation level in effect. If your application is not updating the rows, it is much better to DECLARE those cursors as unambiguous by adding FOR FETCH ONLY or FOR READ ONLY clause to improve concurrency.
But we deviated from our discussion of BLOCKING of cursors to explain about the difference between ambiguous and unambiguous cursors. So, you introduce BLOCKING so that DB2 can fetch the rows in bulk by setting BLOCKING option. This can be set when you pre-compile your embedded SQL program by specifying BLOCKING parameter to either ALL or NONE or UNAMBIGUOUS as explained above.read more
DB2 provides a tool db2dclgn that you can use effectively as an aid in your C/C++ or Java coding efforts. The example shown here is for embedded SQL in C.
Create a table for demo
CREATE TABLE "EXE"."CUTION" (
"UT_UD" BIGINT NOT NULL ,
"UT_ALLOC_ACCT_UD" BIGINT ,
"UT_APP_ID" VARCHAR(32) ,
"UT_BRKR_PARTY_UD" BIGINT ,
"UT_CAP_UD" BIGINT ,
"UT_CLEAR_PARTY_UD" BIGINT ,
"UT_CLT_PARTY_UD" BIGINT ,
"UT_CMT_TXT" VARCHAR(128) ,
"UT_CNTRA_BRKR_PARTY_UD" BIGINT ,
"UT_CNTRA_CLRFM_PARTY_UD" BIGINT ,
"UT_CNTRA_TRDR_ID" VARCHAR(32) ,
"UT_CROSS_TYP_UD" BIGINT ,
"UT_DAY_AVG_PX" DOUBLE ,
"UT_DAY_CUM_QT" DOUBLE ,
"UT_DESK_UD" BIGINT ,
"UT_EVENT_TRANS_TS" TIMESTAMP ,
"UT_EVENT_TYP_UD" BIGINT ,
"UT_EXEXGRP_UD" BIGINT ,
"UT_UT_ID" VARCHAR(64) ,
"UT_GVUP_BRKR_PARTY_UD" BIGINT ,
"UT_INST_UD" BIGINT ,
"UT_MKT_LQDTY_UD" BIGINT ,
"UT_PX" DOUBLE ,
"UT_PX_CCY_UD" BIGINT ,
"UT_RSPBL_TRDR_PRSN_UD" BIGINT ,
"UT_SETTL_CCY_UD" BIGINT ,
"UT_SETTL_TYP_UD" BIGINT ,
"UT_SHARE_QT" DOUBLE ,
"UT_SIDE_UD" BIGINT ,
"UT_ORD_AVG_PX" DOUBLE ,
"UT_ORD_CUM_QT" DOUBLE ,
"UT_TRAD_ACCT_UD" BIGINT ,
"UT_TRANS_STAT_UD" BIGINT ,
"UT_TRANS_TYP_UD" BIGINT ,
"UT_UPSTRM_SYS_UD" BIGINT ,
"UT_TRD_DATE_YMD_PART" INTEGER NOT NULL ,
"UT_ENTRY_MINUTE_PART" SMALLINT ,
"UT_MSG_JNL_UD" BIGINT ,
"UT_CR_PROC_ID" VARCHAR(32) NOT NULL ,
"UT_CR_USER_ID" VARCHAR(32) NOT NULL ,
"UT_CR_TS" TIMESTAMP NOT NULL ,
"UT_SUBSYS_UD" BIGINT ,
"UT_DNSTRM_SYS_UD" BIGINT ,
INSERT INTO EXE.CUTION VALUES (1234567890,987463453,'UTILITY',4353262,
NULL,NULL,NULL,'How are you',
INSERT INTO EXE.CUTION VALUES (35367890,987463453,'SOOKING',4353262,read more