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

0
votes
1answer
124 views

How to profile trigger database performance impact

I want to set up some triggers on a DB2 box (LUW, 9.7). The DBAs are concerned that if the trigger fires too often, it will adversely affect general performance. What's a reasonable cheap approach ...
2
votes
2answers
203 views

Not all columns show up on select * on DB2 for IBM i

My client has a table on their IBMi with a foreign key to another table. It's not showing up with a SELECT * FROM lib.table. However, if I specifically mention it in the column list. It does show up. ...
0
votes
0answers
760 views

Insert string content into a blob in DB2 database using a simple SQL query

I am having trouble inserting in a BLOB field some text content (in fact it's an XML, I know there are field-types more suited than BLOB for this type of content, but I simply can't change it). I ...
2
votes
1answer
79 views

CPU Utilization by Long running Query

Is there any way to find how much CPU is getting consumed by a query running for long time in Database?
1
vote
2answers
40 views

Hashed User concept in db2

Is there anything called "Hashed User" in the IBM DB2 version 10.1? Please help required urgently.
1
vote
1answer
69 views

What's the most efficient way to perform a case insensitive search for a word in a text field?

What is the most efficient way to perform a case insensitive search for a word in a TEXT (or CLOB) field? I've been using WHERE LOWER(body) LIKE '%word%' but I think the calls to LOWER() might be ...
0
votes
1answer
40 views

UNION query question

My query is: SELECT BRAND,BRAND_GROUP, SUB_BRAND ,SUM(INCOME) AS TOTAL_INCOME FROM "tema".MMT WHERE BRAND_GROUP IS NULL AND SUB_BRAND IS NULL GROUP BY BRAND,BRAND_GROUP,SUB_BRAND UNION SELECT BRAND,...
0
votes
0answers
194 views

db2audit on a table in a db2 database

I have a table in my database from where records are getting archived continuously. We have come across a solution of configuring db2audit. I have the below understanding on this: Describe the ...
-4
votes
1answer
260 views

Catch exception in DB2 function

I want to catch exception in DB2 function and return 0 if there was an exception. I don't know how the right syntax create function is_decimal(c_data varchar(100)) RETURNS INTEGER begin select ...
0
votes
1answer
74 views

How to know what are the necessary log files for a backup in DB2?

Is there a way to know what are the necessary log files for specific backup? Un example of backup and restore process: db2 BACKUP DATABASE TST1 ONLINE TO '/db2/backup' db2 RESTORE DATABASE TST1 FROM ...
0
votes
0answers
22 views

Create db2 alert for automatic failover

We have two servers in automated failover HADR (TSA installed and haicu configured). How can we create an alert that fires when a failover happens i.e. when the primary server goes down and the ...
2
votes
1answer
399 views

How to know db size by the backup? in DB2

I want to add validation for free space before restore a db2 database. I have the *.001 backup file (compressed). How do I check the estimate db size of that backup? I can't estimate because the ...
0
votes
0answers
22 views

Does DB2 for z/OS have isolation level similar to READ ONLY in Oracle?

Does DB2 for z/OS have isolation level similar to READ ONLY in Oracle? I need to implement several big selects to DB2 and also I need to retrieve consistent data which was commited on time when ...
1
vote
1answer
71 views

How to run db2pd -hadr in batch mode

I am trying to automate some things on DB2 and would like to use a batch file to do this. I create the following batch file test.bat: db2cmd - -w "db2 -tvf test.db2" I then try to do two commands ...
1
vote
1answer
72 views

Is it more efficient to union from sub-query or combine two outer queries?

I searched for this but couldn't find the response I needed. So my apologies if this has already been asked. I'm asking in regards to DB2 but I suppose this would apply, generally speaking, to just ...
3
votes
0answers
120 views

How can I generated a list of all DB2 Objects including Last Modified Date of object on AS400? [closed]

I am going through an audit, of an AS400 application. AS400 has a DB2 database, and I need to generate a list of all DB2 Objects/Tables including their last modified date. Can anyone help me? Thanks
2
votes
1answer
521 views

DB2 cookbook by Graeme Birchall?

Not sure if this question is appropriate here but I believe that it is of some interest for the db2:ers of the site. Once upon a time the DB2 Cookbook by Graeme Birchall could be downloaded from: ...
2
votes
1answer
182 views

How to fix DB2 Unsupported functionality: GENERATED BY DEFAULT

On IBM DB2 I try the following direct SQL: CREATE TABLE users ( user_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1), user_name VARCHAR(16) NOT NULL, email_address VARCHAR(...
1
vote
3answers
236 views

Hows to make a DB2 table read-only?

We need to test how an application will fail when the DB2 database cannot be written to. What is the easiest way to make a DB2 table read-only? Is this considered locking? Thanks in advance, Bert
2
votes
0answers
80 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
1k views

delete records in one table based on multiple column values in second table

I need to insert and delete records in table 1 based on the data in multiple columns in table 2 Example: Table1 and Table2 have identical columns = address, city, state, zip Table1 is the master ...
2
votes
1answer
229 views

How to use a variable date when running a db2 command in a shell script?

I need to use a date variable in a select, like bellow, but I'm getting this error: Expected tokens may include: "". SQLSTATE=42601 How can use these variables in a shell script? day0_T1=`TZ=CST+...
1
vote
1answer
44 views

Database index on FK with only NULL values

We have in our database an index with table cardinality over 10.000 but full key cardinality only 1. The reason for this that all the values for the column are NULL, hence cardinality 1. Does this ...
1
vote
1answer
32 views

Create DB2 on networked drive but managing from Windows

I'm relatively new to DB world and DB2 is very new. Below is what I'm trying to achieve: Install DB2 express C and IBM data studio on windows (with Fixed IP) create/share/host DB2 on a shared/...
0
votes
2answers
187 views

Application == session == connection in DB2?

Can I consider as synonyms Applications, Sessions and Connections in DB2 LUW? In db2top, I see "Sessions". There is a command "db2 force applications", views and other stuff. And from the developer ...
1
vote
1answer
282 views

Named Parameter Markers in DB2 for z/OS

I have a query where I will be using the same value twice for one of the parameters. This query will be run under IBM Data Studio to see the results. Below is a sample query: SELECT * FROM SYSADM....
2
votes
1answer
289 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 ...
1
vote
1answer
76 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 ...
0
votes
1answer
309 views

DB2 Trigger Error : An unexpected token “OR INSERT” was found following SQLCODE=-104, SQLSTATE=42601, DRIVER=3.68.61

I am getting an error while executing the following queries. As per the DB2 documentation, we can have both update and insert event in the same trigger, but when I am trying to do so I am getting an ...
2
votes
0answers
48 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, o....
1
vote
1answer
45 views

Check if encrpytion key is valid in DB2

I have a DB2 table where one of the columns is encrypted using the two parameter variant of ENCRYPT (so the password is supplied in the SQL itself). Unfortunately due to an ETL issue the column got ...
1
vote
1answer
156 views

Syntax error while creating DB2 Trigger

What is wrong in the below trigger syntax, I am getting this error An unexpected token "INT" was found following " DECLARE NULL_ROW_ID". Expected tokens may include: "END-OF-STATEMENT".. SQLCODE=-...
1
vote
0answers
58 views

MQT automatic `set integrity off` when underlying table updated

Have a slow query. Need an MQT so the optimizer will rewrite the query and use my MQT instead. Problem: a manual full refresh is much much faster than using refresh immediate type or a staging table: ...
1
vote
0answers
142 views

DB2 multiple on delete triggers causing deadlock

When trying to run a delete query on the parent table I am getting locked. Following is the table structure and executing this " DELETE FROM PARENT WHERE PARENT_ID = 1; " would create a deadlock - ---...
1
vote
0answers
59 views

Where is default data directory in DB2?

I have a problem: cannot backup DB2 8.1 with backup command line in Windows 2003. I think my HDD was failure (after read error showed by backup command). So, I want to copy the folder/ directory which ...
1
vote
0answers
304 views

How to create a Linked Server from SQL Server 2008 to DB2?

( This is an edit...the real post was really big ) I'm having problem with my SQL Server 2008 to DB2 linked server. I'm getting this error: TITLE: Microsoft SQL Server Management Studio The ...
1
vote
0answers
27 views

db2 Rollforward create active log in the active directory in v10.1

I have one master and one slave DB, they are not in HADR. for each 15 mins, the master archive log will be rsync to the slave DB and do the rollforward to end of logs in slave. But I find out in ...
1
vote
2answers
195 views

DB2 10.5 - When inserting, the database is trying to reuse index keys on GENERATE ALWAYS

I am trying to insert a row into a database table which has a GENERATED ALWAYS primary key column. The table has over 1000 rows, and the primary keys start at 1. When I try to do an insert, the error ...
1
vote
1answer
88 views

Driver properties for db2jcc in Squirrell and MON_GET_CONNECTION table function

I'm trying to set driver properties like: clientAccountingInformation clientApplicationInformation clientProgramId clientProgramName in Squirrell 3.6 (http://squirrel-sql.sourceforge.net/). See ...
1
vote
2answers
1k 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
398 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 ...
0
votes
1answer
514 views

Procedure doesn't have compatible arguments

Getting an error while invoking my procedure named LOG_MSG_TEST(). Procedure Schema name is ADMINIST. My procedure has one INTEGER input parameter and one VARCHAR output parameter. I am calling ...
1
vote
3answers
408 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 ...
0
votes
1answer
62 views

Getting error while installing log4db2 utility

DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0601N The name of the object to be ...
0
votes
1answer
77 views

limit keyword support in DB2 latest version

I could see limit keyword is supported in latest version v10.5.500.107 5 of db2 by default without setting MYS compatibility vector. My installation was DB2 Express-C. Is there any announcement for ...
0
votes
0answers
14 views

I am trying to install log4db2 utility in db2 for procedure logging but I am getting error mentioned below [duplicate]

DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0601N The name of the object to be created ...
1
vote
0answers
358 views

Return both Result set AND a warning from a stored procedure

In Data Studio when you call SYSPROC.ADMIN_CMD() directly and it returns a warning, the results panel shows both the warning and the result set: CALL SYSPROC.ADMIN_CMD('EXPORT TO /tmp/exportcat/...
1
vote
1answer
125 views

Extract column definitions from an IXF file of unknown origin

Suppose someone exported the table PROPERTIES using the command: CALL SYSPROC.ADMIN_CMD( 'EXPORT TO /tmp/properties.ixf OF IXF MESSAGES ON SERVER select * from properties' ); Since then the ...
0
votes
1answer
19 views

Completion Counting only Once

Have an SQL query for DB2 that returns the timestamp of when an event completed. It completes at 2 different date/times, which is the result expected. That result completes at those 2 different times ...
2
votes
2answers
2k views

How can I know my database version?

I need to know what is my database version. I'm using DB2 V9.7 on linux 64 bits. I'm connected to a database and i need to know if it is 32 or 64 bits. Can someone help me?