The IBM DB2 Enterprise Server Edition is a relational model database server developed by IBM. It primarily runs on Unix (namely AIX), Linux, IBM i (formerly OS/400), z/OS and Windows servers.

learn more… | top users | synonyms (1)

2
votes
2answers
1k views

Syntax changes between DB2 for i (DB2/400) v5r4 and v6r1

Does anyone know where can I find list of syntax changes between DB2 for i v5r4 and v6r1? We are currently migrating a backing database for an application from DB2 for i v5r4 to v6r1 and found out ...
2
votes
1answer
958 views

Does DB2 have equivalents for Oracle's UTL_ENCODE and UTL_RAW modules?

I'm looking for something similar to: utl_smtp.command(conn, utl_encode.base64_encode(utl_raw.cast_to_raw(<username>))); I couldn't find any reference among the DB2's system-defined modules ...
2
votes
1answer
834 views

copy schema across different physical databases in db2

Is there any way to programatically copy across schema (i.e. tables,functions,views) etc from one database over to another one. Assuming both are running same versions of DB2. I found a few utilities ...
2
votes
1answer
250 views

How do you convert IBM DB2's recursive increment to MySQL version?

DB2 Queries: WITH TABLE1 (YEAR_END) as( VALUES (2011)) , TABLE2 (YEAR_END) AS ( VALUES(2011)) , TABLE3 (YEARS) AS ( SELECT 0+2008 FROM TABLE1 T1, TABLE2 T2 union ...
2
votes
0answers
43 views

Error in loading data using DB2 load command in DB2 10.5

I have a IBM DB2 database in our application. We have a monthly Java batch process which loads data in the tables using "load Replace" sql command. Around millions of record will be updated every ...
2
votes
0answers
61 views

How to check wether a DB2 source table and their MySQL MyISAM duplicate are identical?

I want to compare two big tables if they are identical. If I update/insert one table into the other the process takes about 11 hours. What I am looking for is like a sum function which sum ups a key ...
2
votes
0answers
26 views

DB2 Dynamic Load from cursor

We have to load a number of huge tables using Load From cursor. The query formed for the same is a dynamic query as the column names are to be specified. we do the same using the below code in a ...
2
votes
1answer
124 views

How to connect DB2 with eclipse on mac?

I tried to connect the DB2 Express-C Version 10.1 to Eclipse Mars but it returns an exception when I tried to run the program. com.ibm.db2.jcc.am.DisconnectNonTransientConnectionException: Error ...
2
votes
0answers
23 views

Need to read the DB2 DAS parameters

I need to read the DB2 DAS Parameters via any method other than DB2 Command "get admin configuration". I need to read the parameters like EXEC_EXP_TASK or SCHED_ENABLE from file or database. I ...
2
votes
0answers
62 views

Options for copying data out of continuously updating tables

I work with a pretty large (O(10^1) TB) DB2 (LUW, v. 9.7) database. It's got data coming in on a continuous basis via Golden Gate replication from another DB2 database. It's used for business ...
2
votes
1answer
211 views

How to resolve foreign key references across different databases (iSeries and SQL Server)

I've got a system that has a DB2 iSeries database. We are rewriting a portion of the system and implementing it using SQL Server and Entity Framework. Originally, I was lead to believe that there ...
2
votes
0answers
43 views

How to order by fields of joined table while selecting for update?

For selecting some rows of table Fwt_Chipkarte for update I use the following SQL statement (IBM DB2): SELECT o.Archiviert, o.Id, o.IdComm, o.Kartennummer, o.Konto, o.LetzteAenderung, ...
2
votes
0answers
39 views

How to reinstall a legacy DB2 Server 7.x for Linux?

We have to move our legacy database dump to a new Linux installation. I know DB2 7.x is out of support for over a decade, but is there a way to obtain a DB2 7.x Server installation media for Linux? ...
2
votes
0answers
61 views

Optimization suggestions for db2 materialized view

I have a view for db2 9.7 which has consolidated o/p from few tables. The view is built and it is working fine I want optimization suggestions on the view SQL CREATE VIEW XYZZ AS SELECT ...
2
votes
1answer
62 views

Adding a column to the view which shows the date in format YYYYMM

In the following view, I want to add a column (not alter the view) named say "ABC" which shows the date in format YYYYMM (no date, only year and month). How can I do that? I am using db2. create view ...
2
votes
0answers
513 views

IBM DB2 Command line Window - Administrator disappears on windows server 2008 r2?

I have installed IBM DB2 version 10 on windows server 2008 r2 machine. However i am unable to open it's command line window. For example: Whenever i try to open Command Windows - Administrator it ...
2
votes
2answers
292 views

DB2 db2fm proccess

Server is been up for 365 days, however i got some weird repeated procceses. Are these normal? ps -fea | grep db2fm db2inst1 643284 229516 29 May 25 - 212564:06 ...
2
votes
0answers
702 views

Cannot install DB2 Express C 10.1 on Mac OS X 10.7.5 [duplicate]

When I invoke ./db2setup it cannot find the JRE where it expects it. Binary for Java not found as follows: /private/tmp/db2/expc/db2/macos/install/db2setup: line 606: ...
2
votes
1answer
641 views

Can High Water Mark be cleared?

Lately I experience below errors when restoring db2 database SQL2043N Unable to start a child process or thread SQL1084C Shared memory segments cannot be allocated db2stop and then db2start ...
1
vote
2answers
525 views

DB2: Cannot use RUNSTATS/REORG in trigger definitions?

CREATE TRIGGER MY_TRIG AFTER INSERT ON MY_TBL FOR EACH ROW BEGIN IF SOMECONDITION THEN DELETE FROM MY_TBL WHERE PK IN (SELECT PK FROM MY_TBL WHERE SOMEOTHERCONDITION FETCH ...
1
vote
2answers
82 views

Best approach to maintain a single set of DDLs and DMLs for multiple database type

In our application, we support multiple types of databases namely Oracle, IBM DB2 (both UDB and ZOS), postgresql and So on. Currently we are maintaining separate DDLs and DMLs for each of these ...
1
vote
1answer
1k views

Testing if temp table exists in iSeries SQL stored procedure

What would be the syntax for testing if there is already a global temporary table in DB2 for IBM i 7.1? I tried this after DECLARE GLOBAL TEMPORARY TABLE: SELECT * FROM QSYS2.SYSTABLES WHERE ...
1
vote
2answers
4k views

set integrity not enforced in DB2

I am a bit confused with the SET INTEGRITY concept in DB2, why we can OFF the set integrity check to put the table in PENDING state ? does that mean that we can neglect the constraint definitions on ...
1
vote
2answers
86 views

How to get the next value of sequence?

Could somebody tell what is wrong with obvious query below: db2 => select next value for schema_name.sequence_name; As a result I'm getting: SQL0104N An unexpected token "END-OF-STATEMENT" ...
1
vote
1answer
66 views

what is the formal statement terminator with db2 sql

I have added / character as statement terminator all in my db2 statements. I can run sql file in data studio client without any issue. But when I run the sql file in db2 command prompt by issuing db2 ...
1
vote
2answers
71 views

What is the impact on a running database when OS date is changed and set to past

My SAP back end db is db2. Need to understand the impact on this running database (db2 under HPUX) if I change the OS date and set it to a past date please. Thanks.
1
vote
2answers
102 views

DB2 database -> does it continue to be platform-specific? (iSeries, LUW, z/OS)

Wikipedia says DB2 is no longer Platform-specific: Historically and unlike other database vendors, IBM produced a platform-specific DB2 product for each of its major operating systems. However, in ...
1
vote
1answer
311 views

Transaction(s) still alive: How do I find the cause?

I can tell that something is hogging the transaction log on the DB2 for one of our Websphere Commerce installations. Basically I have a burst of transaction log files that are timestamped 5 days ago. ...
1
vote
1answer
2k views

SQL1641N error on linux while running db2start using db2 express c on linux (LUW) but the file system is not mounted using nosuid

I am trying to install and start db2 on my linux machine using the following script: #!/usr/bin/env bash MKDIR=/bin/mkdir RM=/bin/rm TAR=/bin/tar ...
1
vote
2answers
4k views

Adding User access to DB2

I have installed DB2 10.1 in a Red hat box. During the installation I was asked to create several users (db2inst1, db2fenc1 etc). I have been using db2inst1 to perform all operations with DB2 and it ...
1
vote
4answers
774 views

Backup and restore of RDBMS(Oracle, DB2, SQL Server) from Perl

What could be the best approach ? Till now could figure out the following (Oracle) Calling exp.exe and imp.exe utilities in Oracle client using Java and inturn call it from Perl. Calling exp.exe ...
1
vote
1answer
594 views

What does HSJOIN mean in an Explain Plan?

I have the following EXPLAIN PLAN results from a query on my DB2 database. 0 SELECT STATEMENT ( Estimated Costs = 5.928E+02 [timerons] ) 1 RETURN 2 HSJOIN 3 [O] ...
1
vote
2answers
46 views

How can I ensure that only one user at a time can call a stored procedure? Can I lock an SP?

I have a procedure that generates reports basis user requests. The request is such that at a time only one user can call the procedure. Is there any way I can handle this using DB2? Like place a lock ...
1
vote
1answer
34 views

Multi event Trigger syntax

I have a db2 LUW 10.5 database. I want to create a trigger that saves a copy of a record into an audit table whenever, a record gets updated and inserted. I came up with follwing syntax: CREATE ...
1
vote
2answers
666 views

SELECT * FROM INFORMATION SCHEMA on V5R3M0

I have access to a client's V7R1 AS/400 and I can run queries like SELECT NAME FROM INFORMATION_SCEMA.TABLES and SELECT COLUMN_NAME, DATA_TYPE, TYPE_NAME, COLUMN_SIZE, ...
1
vote
3answers
256 views

AUTO_BACKUP not even trying to backup database

I've setup 2 identical linux servers with db2 10.5 . AUTO_BACKUP only works on one of them. Also, no health notifications come from that server at all, although postfix on localhost is running and a ...
1
vote
1answer
57 views

Is there any way to trace optimizer's work in DB2?

Just like the MEMO structure in SQL Server which is kind of a "paper trail" of steps the optimizer takes in optimizing the query, is there anything in DB2 through which I can get the information like, ...
1
vote
1answer
456 views

DB2 - question regarding RESTORE OFFLINE backup and Archive Logs

I configured last night DB2 to be in Archive Mode. I created required offline backup at 23:00 last night. First online backup will be created at Friday night. Archive logs now keep coming to the ...
1
vote
1answer
613 views

ONLINE BACKUP is not performed from the CRON Job on LINUX server (manually it works)

I made a script which executes successfully online backup when it is run manually by the instance owner. Script is the following (two lines originally). #!/bin/bash /opt/ibm/db2/V9.7/bin/db2 ...
1
vote
1answer
1k views

Remove single quotes from db2 column name

I have following query which i am trying to execute against db2 database server SELECT REPLACE('\'abc\'','\'','') FROM TN_DAY_PROCESS I am expecting the output like abc without single quotes from ...
1
vote
1answer
216 views

db2 connecting to a db via alias and storing user name and password

I created a TCP node and I catalogued a db into it. The catalogued db needs user name and password to be accessed. Is there anyway I can store username and password in the cataologue (or other way)? I ...
1
vote
2answers
241 views

query to check all SCD tables that have data for specific date

It's easy to find all SCD tables through select * from syscat.TABLES t join syscat.COLUMNS s on t.tabname = s.tabname where s.COLNAME = 'EFFECTIVE_DTE' and t.type='T' ...
1
vote
1answer
283 views

Cannot execute stored proc linked to C routine in DB2

DB2 version: 9.7.0.6 OS: RHEL 5 Here are the steps I took to create and bind a C routine to DB2: 1> Created myprog.sqC file 2> Executed db2 prep myprog.sql bindfile 3> Executed db2 bind myprog.bnd ...
1
vote
1answer
2k views

IBM DB2 - store large files

I want to store large files in a DB2 database and I need more information about which is the best technique to use as I am new in this IBM product (previous experience with T-SQL only). The ...
1
vote
1answer
404 views

Analyzing TCB Index Status for DB2

The Following is the TCB index that I could get using a command for my DB2 database. I don't know how to analyze it. Which columns are important? What do they mean? TCB Index Stats: Address ...
1
vote
1answer
26 views

DB2 Reorg-recommended commands

Is there some full list of commands that can cause table to become reorg-pending? I tried google it for a while, but can't find full list. My exact problem for now - can ADD FOREIGN KEY statement ...
1
vote
1answer
27 views

DB2 - cannot invalidate a view

I work on something and I need to check some views. I use this code: SELECT SUBSTR(T.TABNAME,1,32) AS TABLE, SUBSTR(T.TABSCHEMA,1,32) AS SCHEMA, T.STATUS, T.TYPE FROM SYSCAT.TABLES T WHERE T.TABNAME ...
1
vote
1answer
32 views

Set uncommitted read as default in Oracle SQL Developer with DB2 database

I am using SQL Developer with a IBM DB2 database. I am only querying the data for analytical purposes - never making any updates, additions or deletions. I was wondering if there is a way to ensure ...
1
vote
1answer
54 views

Why this statement invalid: `VALUES ( CURRENT PACKAGESET )`?

I'm on db2 luw 10.5. db2 special registers can be set with: SET CURRENT ... and retrieved with: VALUES CURRENT ... with the exception of CURRENT PACKAGESET. For some reason the following ...
1
vote
3answers
272 views

REPLACE INTO SELECT from table

Is there a way in DB2 to replace the entire table with just selected rows from the same table ? Something like REPLACE into tableName select * from tableName where col1='a'; (I can export the ...