Video Tutorial-How to Find Empty/Blank Log files in Active Log Space.
db2flsn -db test -lsnrange
db2flsn -db test -lsnrange log_number
db2flsn -db test -lsnrange -startlog log_number -endlog log_number
This post is useful for DB2 developers, administrators, database architects to learn how to access Oracle database stored procedures from DB2 database sources
A federated system is a special type of distributed database management system (DBMS). A federated system consists of a DB2® database system that operates as a federated server, a database that acts as the federated database, one or more data sources, and clients (users and applications) that access the database and data sources.
The power of a federated system is in its ability to:
1. Correlate data from local tables and remote data sources, as if all the data is stored locally in the federated database
2. Update data in relational data sources, as if the data is stored in the federated database
3. Move data to and from relational data sources
4. Take advantage of the data source processing strengths, by sending requests to the data sources for processing
5. Compensate for SQL limitations at the data source by processing parts of a distributed request at the federated server
A federated procedure is a federated database object that references a procedure on a data source.
Federated procedures are not alternative names for data source procedures in the same way that aliases are alternative names. A federated procedure is defined at the federated database but calls a data source procedure when the federated procedure is invoked. Because the federated procedure is a federated database object, users and client applications can invoke the data source procedure logic by calling a federated procedure. The results of the data source procedure, such as the output parameters, are returned by the federated procedure. Using a federated procedure makes the location of the data source procedure transparent to users and client applications. You use the name of the federated procedure to call the data source procedure.
A federated procedure is to a remote procedure what a nickname is to a remote table. Nicknames and federated procedures are objects on the federated database. A nickname is an object that references an object, such as a table or view, on the data source. With a nickname, you query a data source object. With a federated procedure, you call a data source procedure.
You use the CREATE PROCEDURE (Sourced) statement to register a federated procedure and use the CALL statement to call a procedure. You can embed the CREATE PROCEDURE (Sourced) statement in an application program or issue the statement with dynamic SQL statements.
The CREATE PROCEDURE (sourced) statement can be used to link a remote stored procedure:
- "stand-alone" or in a package - after the SOURCE keyword you could define the procedure you want federate as, in the most complete version, source schemaname.packagename.procname.
- with zero or more input and/or output parameter - in case you have some parameters, after the source name, it's enough define the keyword NUMBER OF PARAMETERS followed by the number of parameters (not the type).
This example shows how to use the CREATE PROCEDURE statement to create a federated procedure for a data source procedure on Oracle.
Step 1: Original Oracle DB source procedure code
CREATE OR REPLACE PROCEDURE ORASCHEMA.TESTFEDERATION (p_in IN VARCHAR2(10), p_out OUT INTEGER)
v_count INTEGER := 0;
p_out := v_count;
Step 2 : Create a DB2 procedure referring to the oracle source procedure code
CREATE OR REPLACE PROCEDURE DB2INST1.REM_TESTFEDERATION
NUMBER OF PARAMETERS 2
FOR SERVER TSMEDB
DB2INST1.REM_TESTFEDERATION=> This is the DB2 name referring to the Oracle source procedure
ORASCHEMA.TESTFEDERATION => This is the Oracle source procedure name
Number of parameters => 2
TSMEDB => This is the server name created by federation setup steps
Below are for reference on Oracle DB:
a. db2 create wrapper orawrapper library 'libdb2net8.a'
b. db2 "create server TSMEDB TYPE ORACLE VERSION 11.1 WRAPPER orawrapper
OPTIONS (ADD NODE 'TMP_NODENAM')"
c. db2 "create user mapping for db2inst1 server TSMEDB options
(ADD REMOTE_AUTHID 'col_team', ADD REMOTE_PASSWORD '*******')"
SPECIFIC => Oracle procedure name
Step 3: To test the procedure just call it
db2 "call DB2INST1.REM_TESTFEDERATION('a',?)"
These posts are my opinions and do not necessarily represent IBM’s positions, strategies, or opinions
mailto :- ramyeles@in[dot]ibm[dot]com
The Bengaluru DB2 Users Group is excited to announce its upcoming Users Group Conference, scheduled for Saturday the 21, June.
The meet, lined up with an amazing set of speakers, promises to provide a greater understanding and enablement of products using DB2 and more. So if you are a DB2 expert or, simply an eager learner then join us for the event happening at the IBM office, Embassy Golf Link, Bengalore.
Pre-register for the event by sending an email to email@example.com with "I will attend" as the subject line.
Encourage you to share the information to other DB2 users in your company.
The db2pd command is used for troubleshooting because it can return quick and immediate information from the DB2 memory sets.The tool collects information without acquiring any latches or using any engine resources.
On a particular Windows system, db2pd would fail to execute. For a few db2pd commands, the control would just return to the command prompt without returning any information, and for few others - like 'db2pd -stack all' - it would return error 32.
db2pd was trying to start a service on windows and it kept failing with error 32. This corresponds to,
The process cannot access the file because it is being used by another process.
So , the db2pd.exe with PID 4336 first creates the service with an executable C:\Program Files\IBM\SQLLIB\BIN\db2bddbgsvc.exe.
This is expected. Then db2pd.exe activates services.exe ( PID 736 ) and the services.exe tries to start the newly created service ( as can be seen in the stack trace ) .
During the service start up it finds and traverses the directory "C:\Program" and hits the "SHARING VIOLATION" error .
On further investigation, it was found that, there was a folder 'C:\Program' which was there on the system, and contained some old files. Once this folder was renamed, the db2pd started working fine.
This article provides detailed information about the front-end and back-end processes of IBM DB2 Command Line Processor (CLP) tool. It also discusses how these two processes communicate using IPC queues to execute a command. We have seen many PMRs in this area related to IPC queues and CLP processes. This article will help user, developer and L2 to understand the command execution process and CLP behavior in case of an error. With this knowledge, it is easy to understand the process flow in db2trace files. This article provides only a high level abstract about how IPC queues are used by CLP processes.
To execute a command using IBM DB2 Command Line Processor, IBM ships two executables named as 'db2' and 'db2bp'. On Windows, these are named as db2.exe and db2bp.exe. We can find these executables under <install_dir>\bin directory. These two executables run as two separate processes to execute a command. db2.exe is the front-end process (FP) and db2bp.exe is the back-end process (BP). FP interacts with user and BP remains in back-ground.
FP takes command from user as input, parses it and sends it to BP for execution. BP maintains the connection with database. BP gets created by FP for the first CLP command and remains in back-ground until user issue 'db2 terminate' command to terminate the BP. BP sends SQL statement to server for execution, receives the result from server and passes the result back to FP. FP formats and return the result back to user. FP acts as the user innterface.
FP and BP are two independent processes. Both processes FP and BP use IPC queues to communicate with each other. Each db2 command starts a new FP and FP gets terminated after passing the result to user. All front-end processes with the same parent are serviced by a single back-end process, and therefore share a single database connection. Two FPs with different parent process id (PPID) uses two different BP for command execution. BP terminates itself if it finds that parent process of FP no longer exists.
There are three IPC queues named Request Queue (RQ), Input Queue (IQ) and Output Queue (OQ) used by CLP. CLP uses PID of FP to construct name of OQ and PPID of FP to construct name of RQ and IQ. FP creates OQ at start and deletes OQ at end of the execution. BP creates RQ and IQ at start and deletes RQ and IQ when BP gets terminated. Name of RQ, IQ and OQ gets constructed by FP as below. These are the name of IPC queues on Linux platform.
RQ: R115943308A24788 = "R11" + ppid of FP(5943308) + "A" + uid of current user (24788)
IQ: I225943308A24788 = "I22" + ppid of FP(5943308) + "A" + uid of current user (24788)
OQ: O331198235A24788 = "O33" + pid of FP(1198235) + "A" + uid of current user (24788)
RQ is the primary link between FP and BP. RQ and IQ gets identified by PPID of FP, but FP does not create these queues. Only BP creates and deletes RQ and IQ. FP sends its PPID to BP as an argument during creation of BP. We can see these queue names in the formated db2 trace file collected using db2trc tool as below:
20721 entry DB2 UDB command line process clp_get_qhandle fnc (188.8.131.52.0)
pid 6767 tid 46912625280640 cpid -1 node -1
Data1 (PD_TYPE_STRING,14) String:
20728 entry DB2 UDB command line process clp_get_qhandle fnc (184.108.40.206.0)
pid 6767 tid 46912625280640 cpid -1 node -1
Data1 (PD_TYPE_STRING,14) String:
To understand the flow of command execution, let us take connect command "db2 connect to sample" as example. Also, assume there is no BP running.
On command prompt user has entered "db2 connect to sample" command
-> db2.exe gets "connect to sample" as argument, so FP started
-> FP constructs the name of IPC queues RQ, IQ and OQ
-> FP tries to open RQ
-> FP get error as RQ does not exist that implies there is no BP to serve this FP and a new BP should get created because only BP can create and delete RQ (and IQ).
-> FP runs system command with PPID to start BP
-> BP gets created. BP creates RQ and IQ. BP installs interrupt signal handler too. BP waits on RQ to receive request from FP for command execution.
-> To make sure BP got created, FP tries to open RQ.
-> If FP get RQ handle successfully, open IQ.
-> FP creates Output Queue.
-> FP installs interrupt signal handler.
-> FP parses the command, check syntax and type of command.
-> FP writes a request to RQ for new command execution. Request contains PPID of FP and OQ handle too.
-> When a FP request is received in the RQ, the backend process does the following:
-> Writes in the output queue to tell the FP to send command in the IQ.
-> FP reads OQ and writes command for execution to IQ.
-> FP waits on OQ to get result from BP.
-> BP reads command from IQ and sends request to server for connect.
-> BP receives response from server
-> BP writes the result received from server to OQ.
-> BP goes to IQ and waits there for next instruction by current FP.
-> FP reads the result from OQ.
-> FP writes the formatted result on stdout.
-> FP writes to IQ that it has finished execution.
-> BP reads instruction from IQ and goes to RQ to serve request from next FP. BP continues to wait here in background.
-> FP closes its RQ and IQ handle, deletes OQ and finishes execution.
Since there is only one back-end process to server request from all front-end processes having same parent process id; back-end process can execute only one command at a time. Request from multiple front-end process having same parent process id gets served by back-end process in serial.
To control the communication between FP and BP using message queues, CLP uses four environment variables named: DB2BQTIME, DB2BQTRY, DB2RQTIME and DB2IQTIME. We can get values of these environment variables by executing "db2 LIST COMMAND OPTIONS" command. Above Infocenter page provides detail about these environment variables too.
Cloud computing has almost become a rage among IT industry and businesses today. It is transforming the way organizations create software, applications and the way they do business. The fundamental need of focusing on core business, controlling IT expenditures and adaptability to changing business ecosystem is driving companies to move to cloud.
Establishing a in-house data warehousing and business intelligence (BI) environment is not a trivial task, organizations have to spend millions of dollars to procure hardware, software and then spend months in installation, configuration and optimization before they could actually start using these systems. In addition to this, top it up with the investment in resources for continuous administration and the periodical hardware upgrades to manage growth and keep the momentum going.
All the above factors combined together make it very compelling for companies to make a radical shift of some of their on-premise analytical data warehouse environments to cloud. It simplifies and speeds up analytics without the need of deploying heavy weight infrastructure and teams. On-demand resource provisioning helps in accommodating real-time workload surges without much manual interference. Imagine having a plethora of compute capacity lying idle in server rooms for once or twice a week reporting vs. having it on cloud and only paying for the required usage.
However, irrespective of these innumerable benefits which a cloud can provide it still involves certain challenges which may make businesses wary of putting their data warehouses on cloud:
What type of data can be put on public cloud?
How secure it is to put sensitive organizational data on public cloud?
What volume of data can a cloud environment support? Loading huge amount of data which is very typical of a data warehouse requires high bandwidth, how efficiently can a cloud handle that?
Performance of a virtual machine on cloud may not match that of a bare metal server.
This may impact the complex analytics being performed on a data warehouse.
What could be the impact on business due to loss in transaction latency arising out of communication over a network (large distance between datacenter and users and/or lower bandwidth), especially in financial world?
There are several vendors who now provide data warehouse and BI as a service but everyone may not be able to handle the complexity of a data warehouse and analytics ecosystem on cloud.
IBM’s BLU acceleration on cloudis an offering which provides a self-service BI and data warehousing on cloud using best in class security and other features to support even the most complex production environments. It is powered by IBM DB2 with BLU Acceleration a next generation in-memory technology. Columnar data processing and high compression rates combined with an enterprise class BI and DW tools like Data Architect, Cognos and compatibility with R help customers transform their data into insights at speed of thought. Through BLU Acceleration on Cloud now even small organizations who could not afford to establish data warehouses earlier can have access to one of the most advanced analytical environment and make the best out of their data at a very low cost.
BLU Acceleration on cloud is available on IBM Softlayer and Amazon Web Services (AWS). I am really excited to invite you to get hands on experience of the technology through a Free Trial (in Beta). Do let us know your feedback or any queries which you may have on this forum.
Although I work for IBM, the views expressed are my own and not necessarily those of IBM and its affiliates.
What are database logs? They are minimal records generated by transactional writes to keep track of database changes. In all senses, they are nothing but side products generated out of write operations within transactions; and are not something served out of, in response to users’ data queries. Moreover, DBMS incurs CPU, memory and storage resources owing to generation and maintenance of these logs. An obvious question next to this would be why someone on earth should care for stuffs that eat system resources and do just nothing? If you are a newbie in the field of system administration, and at this point ready to turn off database logging forever; hold on your decision until you read this post completely. Data management has always been a serious affair regardless of the type and/or size of the business dealing with it. Nobody would rejoice investing on something having no significant value add. So, logs are required and important in much greater extent. How? Let’s understand that.
Transaction writes facilitate applications performing changes to database’s data. By fundamental virtue, they steal database consistency at the beginning, and re-establish it at the end. In other words, during execution, transactions make database inconsistent. In addition, like any other system, failure of a DBMS is not uncommon. Situations like power outage, OS or software crash, disk corruption are unavoidable. However, under any circumstances, businesses cannot afford loosing data, and hence DBMS vendors must ensure database recovery across points of failures. Scenarios, wherein DBMS is brought down disgracefully, database is typically left inconsistent owing to abnormal termination of ongoing transactions. Applications, if allowed to connect to the database at this state, would certainly fetch some inaccurate data, which a business can never accept. Therefore, post such failure, DBMS must bring database consistency back before making it available for use. While doing this DBMS would need to refer to the history of transactional changes happened to the database before failure; but, wait…history? Where is it? Relax…DBMS won’t let your business down. Here comes the utility of transaction logs, whose credibility was being questioned sometime back. Recall that they kept on consuming system resources to keep track of the changes your database was undergoing during its good times. Now, it’s their turn to pay back. DBMS would read individual log records stored within log files sequentially, and perform the following.
Redo the effects of committed transactions that could not make their way to database disks before the failure.
Undo those pertaining to uncommitted (i.e. ongoing / in-flight) transactions that somehow got persistent into database storage space.
At the end, your database will get the lost data back and regain its consistency. Henceforth, applications are guaranteed to see correct data whenever connected to the database.
In another kind of failure situation, your database storage may completely or partially go bad, and you end up restoring a backup image of the database taken prior to the disk crash. Definitely, this will rebuild your database, but its state will be taken back to the backup time. Should you ask DBMS to take the state of your database forward, log records following the backup timestamp will be read sequentially from the log files and re-applied to the restored database.
So far we discussed the importance of transaction logs in a DBMS, but have you ever wondered that they could be even more precious than the actual data stored within your database? Well, let’s face the truth straight away. Yes, DBMS always cares more for transaction logs than user data. It makes sure that log records are saved in log disks before the corresponding user data is made persistent to the database. Why so? Let’s explore.
Transaction writes bring data pages from disks to bufferpool area of database memory and modifies them per user request. Each modification generates log record in log buffer (different from bufferpool, but collocated with it within database memory). Now, it is not a rare scenario, wherein bufferpool gets full of changed data pages (a.k.a. dirty pages) owing to long running transactions and DBMS needs to make space for new transactions. This is called dirty page steal, where new data pages as requested by new transaction replace the old dirty pages pertaining to other ongoing transactions. Those old dirty pages are placed into database disk. However, before that, DBMS must write their corresponding log records into the log files within log disk. If that wasn’t the logic (i.e. dirty pages were made persistent to database storage without having corresponding log records flushed to files), and a crash (like power outage) was encountered, DBMS wouldn’t find the necessary log records to undo uncommitted changes (that were written to disk). As a result, database consistency cannot be re-established. In a nutshell, transaction logs must go to disk before corresponding data changes do so. In DBMS world this mechanism is known as ‘Write Ahead Logging’ (WAL).
Externalization of log records from memory to disk is initialized when a transaction commits, and/or log buffer gets full, and/or bufferpool gets full. Writing bufferpool data to database disk on the other hand can run asynchronously based upon whether conditions like bufferpool full and/or deactivation of database etc. are met.
Hope this explains the perspective of DBMS treating transaction logs as golden data and why you cannot afford loosing them if data recovery is one of your priorities. I will talk more about this area of database technology in my upcoming posts. Until then, please stay tuned.
Although I work for IBM, the views expressed are my own and not necessarily those of IBM and its affiliates.
I just wanted to share the extra information that I have to ease the migration and also to tell about new capabilities of DB2 embedded in the light of current subject (there are more than that ofcourse. I will try to break the topic in two parts for ease in understanding and also for you to be able to make use of devWorks article mentioned above:
A) Updation to above devwork article link "Common real-time scenarios and their solutions" section:
Starting in from DB2 V10.1 Fix Pack 2 onwards, when compatibility mode is switched on, the following features are supported:
EXEC SQL COMMIT WORK RELEASE;
EXEC SQL ROLLBACK WORK RELEASE;
B) Extending to devwork here with to provide more tips for easing the migration from Pro*C to DB2 Embedded SQL C applications:
Difference in file extensions: '.pc' is the extension for Pro*C code and '.sqc' is file extension for DB2 Embedded SQL C applications. Customers or migration specialist can modify the file extension. One can write a simple script to do that.
Header file inclusion: Pro*C can have include header files in environment variable "include" and as a parallel in DB2 embedded SQL C, you can use DB2INCLUDE environment variable.
Difference in SQLCA structure: The SQLCA is an SQL communication area structure used by the database manager to return error and other information to an embedded application program. This structure contains status of every API call and SQL statement issued. There is a structural difference between Oracle and DB2 though, with respect to text associated with the error code. In simpler terms oracle returns error text in sqlca.sqlerrm.sqlerrml and sqlca.sqlerrm.sqlerrmc but when it comes to DB2 error text is returned in sqlca.sqlerrml and sqlca.sqlerrmc.
Syntax difference EXEC SQL INCLUDE statement: This syntax is supported in DB2 as well, but has some technical differences; without quote header is searched with .h extension, when .h is already present. IN DB2, customers or migration specialist can change "EXEC SQL INCLUDE sample.h;" to "EXEC SQL INCLUDE "sample.h"".
Difference in Select statement syntax with INTO clause: Pro*C can ignore INTO clause in a select statement but DB2 is strict with INTO clause. So syntax change needed to be done as per DB2 syntax.
Late validation of database objects: For non-existent table, like temp created during shell script, you can write SQL and Oracle will validate it at run time but DB2 gives error as it strictly checks for the same. To overcome this problem during PRECOMPILE of embedded SQL application users can convert the error into warning if they use PRECOMPILE option "VALIDATE RUN".
Statement execution for specified connection: EXEC SQL AT syntax and statement level connection setting is not supported in DB2 but you can use DB2 ESQL SET CONNECTION syntax for a block of statements. In DB2 SET CONNECTION statement changes the state of a connection from dormant to current. Multiple connections to the same database can be done in DB2 embedded SQL application as well but you must use multi-threading, which entails using DB2 context management APIs to be thread-safe.
CURSOR and PREPARE statement names uniqueness: In Pro*C application you can reuse same cursor or prepare statement name multiple times but DB2 embedded SQL application is strict and gives error The cursor "C1" is already defined.Eg:EXEC SQL DECLARE C1 CURSOR FOR sql_stmt; EXEC SQL DECLARE C1 CURSOR FOR sql_stmt;
Application developer is needed to use unique name for PREPARE/CURSOR declarations in DB2 embedded SQL applications.
There might be more differences like this which are needed to be considered but these are the difference which I came across until now and thought of sharing. Hope this blog will help you. Happy migration!!! For any question please comment in this blog.
Most of us know or call IBM Data Server Driver Configuration file as db2dsdriver.cfg. It contains database directory information and client configuration keywords and is based on the db2dsdriver.xsd schema definition file. IBM data server driver configuration file can be used with embedded SQL applications, ODBC, CLI, .NET, OLE DB, PHP, or Ruby drivers.
DB2 V9.7 introduced a new environment variable named DB2DSDRIVER_CFG_PATH which can be used to specify an alternate path for IBM Data Server Driver Configuration file. In DB2 V10.1, DB2DSDRIVER_CFG_PATH started supporting multiple paths and in DB2 V10.5, support for custom file name got added.
What this means is,
IBM Data Server Driver Configuration file can have any name rather than default db2dsdriver.cfg. Of course, one needs to specify that name in DB2DSDRIVER_CFG_PATH.
One can have multiple versions of IBM Data Server Driver Configuration file at same or different locations and use whichever is needed via DB2DSDRIVER_CFG_PATH.
This takes off the limitation of a single location and provides some kind of high availability as multiple file paths can be specified via DB2DSDRIVER_CFG_PATH.
Let us consider an example,
On Windows platforms:
set DB2DSDRIVER_CFG_PATH=”c:\temp\a.cfg;c:\program files\sqllib\b.config;c:\sqllib\c.xyz”
Assuming user has set DB2DSDRIVER_CFG_PATH as shown above, and first available file is located at c:\program files\sqllib\b.config, then “db2cli validate” will get displayed similar to below:
db2dsdriver.cfg Schema Validation :
Success: The schema validation operation completed successfully.
The configuration file c:\program files\sqllib\b.config is valid
Let us also talk about the high availability of IBM Data Server Driver Configuration file via DB2DSDRIVER_CFG_PATH.
Considering that an organization has multiple workstations with a DB2 copy installed on each machine. These DB2 copies are required to work with an IBM Data Server Driver Configuration file kept at a centralized location and it's path is specified using DB2DSDRIVER_CFG_PATH. Now think about a situation when this file location is not reachable due to some downtime. What happens when someone runs an application with DB2 datasource pointed within that IBM Data Server Driver Configuration file. How about keeping a local copy of the file which gets replicated at some interval from the centralized location and adding it's path in DB2DSDRIVER_CFG_PATH ?
In DB2 embedded SQL application, users can execute SQL statements from multiple threads using DB2 contexts APIs. Application developers need to use context to switch between the connections and each context is the environment from which an application runs all SQL statements. Each context can be associated with one or more threads within an application. Context switching between the connections is supported in embedded SQL C and C++ applications only. Contexts may be switched between threads in a process but not switched between the processes. Application developers can use multiple contexts to provide support for concurrent transactions in a DB2 embedded SQL C and C++ applications.
How about that ? Too abstract ? Let me be simple and detailed.
A context can be created, attached, detached, and destroyed by using the following APIs in DB2.