DB2 Help

DB2 10.5 on Windows Server 2012 R2

I am trying to add an identity column to a small table (about 700 rows). I see that DB2 will not allow you to directly add it so you have to do something like this...

ALTER TABLE tablename ADD COLUMN id INTEGER NOT NULL WITH DEFAULT 0
ALTER TABLE tablename ALTER COLUMN id GENERATED BY DEFAULT AS IDENTITY
REORG TABLE tablename
UPDATE tablename SET id = DEFAULT

Open in new window

My issue is then I run the UPDATE SET ID = DEFAULT it runs and runs and runs and runs. Looking at LIST APPLICATIONS SHOW DETAIL it shows me the process is compiling.

DBUSER    toad.exe  Compiling  11/16/2018 14:12:41.661005   D:\DB2\NODE0000\SQL00002\MEMBER0000\

Open in new window

So my questions are:

1. Is this the best way to add an identity column to an existing table? Or is there a better way?
2. Why is it taking so long (as of writing this the UPDATE statement has been running for over 30 minutes).
3. What does it mean when it says COMPILING? Is that correct?

Any help would be greatly appreciated. Thank you!!!

Jim

12 Comments

Why Diversity in Tech Matters

LVL 12

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

Question by

Jim Youmans

2018-10-23

DB2 Finding the root cause of crash

DB2 10.5 on Windows

Our production instance of DB2 10.5 on windows crashed Friday afternoon. It would not accept connections and it would not accept DB2STOP command. I finally had to reboot the server and it all came back up OK but now I am trying to figure out what happened and am having a hell of a time. I have all the trap files and bin files and diag log files but there is so much data there and none of it seems to tell me exactly what happened.

Is there a program of process that will examine the files and give me an idea of what happened? Of what files should I concentrate on to find the root cause?

Any help would be greatly appreciated. Thank you!

Jim

8 Comments

Hi Below is my sample data and I need to write a SQL query to derive 6 columns from the column CONTACTVALUE below and this is my CASE statement as per the requirement

SELECT
CASE WHEN CODE ='VOI' AND sub-code='PER' THEN contactvalue END AS PersonHomeTel
,CASE WHEN CODE='VOI' AND sub-code='PRO' THEN contactvalue END AS PersonWorkTel
,CASE WHEN CODE='VOI' AND sub-code='OTH' THEN contactvalue END AS PersonMobileTel
,CASE WHEN CODE='EMA' AND sub-code='PER' THEN contactvalue END AS PersonHomeEmail
,CASE WHEN CODE='EMA' AND sub-code='PRO' THEN contactvalue END AS PersonWorkEmail
,CASE WHEN CODE='EMA' AND sub-code='OTH' THEN contactvalue END AS PersonOtherEmail
from my table

however I need help in modifying my query so that I need to pick up the contactvalue based on the above rules
based on the LATEST date in case of more than one record. So for example for cust_id 1, for code VOI and sub-code PER has 2 records, then the one with LATEST date should only be picked up. If the LATEST date record contactvalue is NULL then the next contactvalue should be picked up based on the next highest date.

can anyone modify my query to meet my requirement ?

cust_id code sub-code contactvalue Date
1 VOI PER me@gmail.com 18/05/2014
1 EMA PER 00447804584 18/06/2014
1 POS OTH 00437804587 18/01/2014
1 VOI PER 00447804687 18/06/2014
1 EMA PER 00478025848 18/06/2014
1 …

12 Comments

db2 export with delimiter set explicit
I execute the db2 command on a windows machine in the db2 command line interpreter shell
I execute a SQL command against db2 and export the result in a delimited ascii file. I want to be able to set the delimiter, default is space with fixed column length.
I start the export with the following command

db2 -tvmf \\somePath\mySQLCommandToExecute.sql > \\somePath\ExportedResults.csv
and the mySQLCommandToExecute.sql file
CONNECT to MYDB USER MYUSER USING MYPW;
With MultipleReferences as(Select distinct coulnm1 , tb1.column2, column3, tb1.column4 from schema1.table1 tb1, schema1.table2 tb2 where column2 > 1 and column3 <> column4 AND COALESCE(tb2.column1,tb2.column2) = tb1.column1 ) select * from MultipleReferences where column4 in('somefilter1', 'somefilter2');
This is only a schematic example of my SQL. As the SQL command is huge (a lot of filters given by a excel sheet) I generate the sql command via a programm and want to have it in a separate file for execution.

As my SQLToExecute is very long I want to have it in a separate file and not directly entered on the db2 command line. So any suggestions like db2 export to test.del of del select tabname from syscat.tables where tabschema ='VSC';
are NOT applicable in my case.
Being able to execute a SQLQuery that is in a separate file is mandatory. Also solution like adding the delimiter explicitly in my SQL command like select a || ';' || b from mytable is not appicable

…

6 Comments

Hi All

I could see that SQL Data Replication is working fine under the same instance (db2inst1) between a source and target DB .

But i have the requirement to replicate the data between two different instances(db2inst1 and db2inst2) which are running on the same DB server, having source DB in db2inst1
and target DB in db2inst2.

May I know the reason why data is not being replicated.

Please let me know if federation is needed for this requirement between db2inst1 and db2inst2 (which are already on the same DB server) , for the SQL replication to become successful.

Kindly provide your inputs.

2 Comments

Hi All

Greetings!!!

I am working on SQL replication set up.

I am facing the below error message when I am starting ASNAPPLY Program on my linux machine. I am using DB2 LUW 10.5 .

The NLS msg is ASN1023E APPLY : "AQ00" : "WorkerThread" : The Apply program cannot open the work file "" because of a system er
ror with ERRNO "”PATH”/AQ00.000". The error code is "2".

Please kindly provide your inputs to resolve this error message.

3 Comments

Hi ALL

Good Afternoon!!!

I am facing below error in ASNCLP while trying to create the Subscription Set. Below are the commands used and error messages reported:

Repl > SET SERVER CONTROL TO DB DB1

====
CMD: SET SERVER CONTROL TO DB DB1;
====

Repl > SET SERVER TARGET TO DB DB2

====
CMD: SET SERVER TARGET TO DB DB2;
====

Repl > CREATE SUBSCRIPTION SET SETNAME SET00 APPLYQUAL AQ00 ACTIVATE YES TIMING INTERVAL 1 START DATE "2018-10-10" TIME "14:35:00.000000"

====
CMD: CREATE SUBSCRIPTION SET SETNAME SET00 APPLYQUAL AQ00 ACTIVATE YES TIMING INTERVAL 1 START DATE "2018-10-10" TIME "14:35:00.000000";
====

ASN1956I ASNCLP : The program now generates the script for action: "CREATE SUBSCRIPTION SET".

ASN1955I ASNCLP : The program will use the following files: "replcap.sql" for the Capture SQL script, "replctl.sql" for the control SQL script, "repltrg.sql" for the target SQL script, and "replmsg.log" for the log file.

--- ASNCLP Version 10.05.07 Build date 2015-12-21 11:26:45

ASN1550E The replication action "Create Subscription Set" ended in error. The value for the input parameter "source server information" is missing.

ASN1954E ASNCLP : Command failed.

Do I need to have and source and target DB names(DB1 and DB2) should have same name to resolve this issue ?

Please kindly provide your inputs.

Thanks in advance!!!

3 Comments

Question by

Jim Youmans

2018-10-05Solved

DB2 training for DB2 10.5 on Windows

DB2 10.5 on Windows

My department actually has some training budget for the upcoming year and I have been asked to submit some thoughts on what would be beneficial for me to attend. I was looking at IDUG but it looks like it is geared towards z/OS and not windows (I did not see a single subject with windows mentioned in the description for this years IDUG).

Does anyone know of any good conferences or seminars that would be good for a SQL Server DBA who is now working with DB2 10.5 on Windows? Maybe something near a beach?

Thank you!

Jim

5 Comments

Hi All

Which snapshot is recommended to analyze the performance issue of Procedure which is doing an update on a table.

Do i need to go for snapshot of application or snapshot of DB.

Please provide me your valuable inputs.

Thanks in advance!!!

13 Comments

Question by

Jim Youmans

2018-09-28Solved

Whats in my DB2 incremental backup?

DB2 10.5 on Windows

Is there any way to see exactly what gets written to a DB2 online incremental or delta backup file? Not the actual data but what tables and/or table spaces are involved? My full online database backup is 330 GB and my incremental and delta backups are in the 170 GB range. That is half the database. I think this is due to the number of BLOB fields in the database but I would like to be sure.

Thanks!

Jim

2 Comments

HTML5 and CSS3 Fundamentals

LVL 12

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Question by

Jim Youmans

2018-09-26Solved

DB2 Transactions and locking

DB2 10.5 on Windows Server

If I have something like this...

START TRANSACTION
CALL WEB SERVICE; (can take up to 8 seconds)
UPDATE TABLE1;
UPDATE TABLE2;
DELETE FROM TABLE3;
COMMIT TRANSACTION;

How would DB2 handle the tables during the life of the transaction? Would it only lock a table when the change is made or would if lock the tables at the start of the transaction? I am not familiar with DB2 transactions and while the IBM info pages describe how they work, none of it answers this question.

I am making the assumption that they are similar to transactions in SQL Server although I do see some references to SAVEPOINTS which I am not sure I understand. I think that might not apply to DB2 on windows.

Thank you so much for any help in advance.

Jim

15 Comments

Hi All

Greetings!!!

I have 2 instances ( db2inst1 and db2inst2) on the same machine. Each instance is having one DB each. I need to copy the data of a set of tables (for example 20 tables) from
instance1 (db2inst1) to instance2 (db2inst2). This frequency of data copy should be daily once. Every time only Delta data need to be copied from source to target.

What are the possible ways of achieving this.

13 Comments

Question by

Prardhan N

2018-09-12Private

Not able to get a Connection for db2Pool

Hi All

Greetings.

I am facing the below Error message in the Application logs:

Not able to get a Connection for db2Pool

Some few orders are failing with above message.

Below are my DBM configurations.
Priority of agents (AGENTPRI) = SYSTEM
Agent pool size (NUM_POOLAGENTS) = AUTOMATIC(100)
Initial number of agents in pool (NUM_INITAGENTS) = 0
Max number of coordinating agents (MAX_COORDAGENTS) = AUTOMATIC(200)
Max number of client connections (MAX_CONNECTIONS) = AUTOMATIC(MAX_COORDAGENTS)

Can you please give inputs to resolve this Problem.

3 Comments

Question by

Jim Youmans

2018-09-12Solved

DB2 Export / Import with BLOB data

DB2 10.5 LUW on Windows Server 2016

I have a few tables in production that have photos (as BLOBs) in the rows. I want to archive them to an archive server and I have tried SSIS but it is very slow due to the fact that SSIS has to write the JPEG to disk and then read it in again.

So someone suggested that I could create an export file with DB2, move the file to the archive server, and then use the load utility to import it.

I have been able to get the export to work, but not the import.

Here is what I have for the export.

db2 "EXPORT TO 'C:\EXPORT\TEST.TXT' OF DELL LOBS TO 'C:\EXPORT\LOBS' MODIFIED BY LOBSINFILE SELECT * FROM Photos FETCH FIRST 1000 ROWS ONLY"

The FETCH FIRST 1000 ROWS ONLY is for testing since there are millions or records.

When I try to import it I use this.

db2 "IMPORT FROM 'c:\export\test.txt' OF DEL LOBS FROM 'c:\export\lobs\test.txt.001.lob' MODIFIED BY lobsinfile INSERT INTO PHOTO_TEST"

It runs but each row gives me the message...

SQL3229W The field value in column 3 is invalid. The row was rejected. Reason code: 1

The columns are
1. ID
2. PHOTO TYPE
3. BLOB of PHOTO
4. LASTTIME

Here is what the test.txt file looks like.

10000002,"JPEG","test.txt.001.lob.0.24442/","2013-10-13-15.40.55.705000"
10000004,"JPEG","test.txt.001.lob.24442.25957/","2013-10-13-15.40.58.112000"
10000006,"JPEG","test.txt.001.lob.50399.26155/","2013-10-13-15.41.00.533000"
…

3 Comments

LVL 17

Question by

Murphey

2018-09-06Solved

SQ Lnames in RPG

Hi Experts,

After creating several tables with SQL (because of the naming I have to use), I have in my Data Library files with names as CUST_00001 and PROD_00001 (as expected),
The field names in the files from DB2 site, are numbered CDTA_00001, CDTA_00002 etc. (also as expected),

I prefer to have the SQL field names in my SQLRPGLE program, so that I can create a readable (maintainable) program, is there a way to manage that?

Thanks

4 Comments

LVL 7

Question by

valmatic

2018-08-31Solved

IBMi DB2 SQL UPDATE over 3 joined tables

Hi. I am a TSQL user trying to make sense of DB2 SQL on the IBMi. I need to populate a field with a percentage of a cost in another field, within the same table. That would be simple enough but I need to filter on field values in 2 separate tables. I think my problem is understanding the joins in DB2.. I put this together but I'm leery of running this code on my IBMi, even over test data.

UPDATE wklibj.itmrvbx831
SET colt = smat * .25 WHERE exists(
    SELECT a.itnbr,a.smat,a.co1t,a.so1t,b.vndnr,c.vndnr,c.ittyp,c.b2cocd
        FROM wklibj.itmrvbx831 a                                    
        JOIN wklibj.itemblx831 b ON a.itnbr = b.itnbr               
        JOIN wklibj.itmrvax831 c ON a.itnbr = c.itnbr               
        WHERE b.vndnr LIKE 'F%' AND c.ittyp IN(3,4) AND c.b2cocd = 'CHN'                                                               
        or  c.vndnr LIKE 'F%' AND c.ittyp IN(3,4) AND c.b2cocd = 'CHN')

Open in new window

This looks like it should run but examples I'm finding online lead me to believe that I need to repeat the filters after my enclosed select statement. I also looked at using a merge statement but not sure it's a good fit here..? thanks.

8 Comments

We have a DB2 10.5 system (on windows) that we are exporting to a SQL Server 2016 data warehouse on a daily basis using SSIS 2013. The issue I am running into is that some of the tables in DB2 have BLOB columns (picture data) and when I use SSIS to import that to SQL Server it is very slow. The main DB2 table has about 25,000 new records per day and it takes about 3 hours to move that from DB2 to SQL Server.

The main bottle neck is that SSIS has to write the BLOB to disk, then read it and store it in SQL Server. I am averaging about 130 rows per second with both systems on solid state drives.

My SSIS data flow is set up like so...

OLE DB Data Souce connected to DB2 10.5
Data Converison task to change DB2 BLOB datatype to Unicode Text Stream (DT_NTEXT)
Derived Column to fix DB2 date to SQL date issue
OLE DB Data Dest to SQL Server 2016

I had the SSIS BLOB storage on C:\temp at first and that was even slower (about 75 rows per second) and I read a blog about why not to do that. So I moved the BLOB to a different drive and that about doubled the speed but at 130 rows per second, it takes about 3 hours to process 25,000.

Is there any way to speed this up? Is there any way to use a memory location to read and write the BLOBs too?

Any suggestions would be much appreciated!

Thanks!!!

6 Comments

Hi All

My DB instance is down and is not getting started. I have checked the diag logs and found this below error.

I am seeing the message(SQL1063N DB2START processing was successful) when i use db2start ,but immediately it is getting crashed and I am not able to see any output
for ps -ef|grep sysc.

ADM0503C An unexpected internal processing error has occurred. All
DB2 processes associated with this instance have been shutdown.
Diagnostic information has been recorded. Contact IBM Support for
further assistance.

Any good inputs please to start the DB instance.

4 Comments

We are using Informatica 10.2 R1 for extract, masking, subset using Informatica's Test Data Manager. We have a large volume of DB2 tables to mask and subset from production on an Iseries and mainframe. The problems we are having are designing entities so that they are small enough to manage. However, not all parent tables are represented for the child tables especially when we run into circular relationships. Can one entity be large enough to accommodate all 720 tables?

1 Comment

LVL 27

Expert Comment

by:Brian B2018-09-04

This is a post. Experts generally don't read the posts area. You should probably ask it as a question using the "ask a question" button.

Add Comment

Starting with Angular 5

LVL 12

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

Question by

Prardhan N

2018-08-27Private

Waiting for last disassociation from the db

Hi All

Greetings.
Can any one help me to interpret the below Informational message which I found in my diag log file.

2018-08-25-06.03.16.668279-300 I10046620E503 LEVEL: Info
PID : 1144 TID : 14068592123456 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPHDL : 0-8 APPID: *LOCAL.DB2.180531042846
HOSTNAME: hostname2
EDUID : 80 EDUNAME: db2redom (SAMPLE) 0
FUNCTION: DB2 UDB, base sys utilities, sqeLocalDatabase::HdrKickPooledAgents, probe:20
DATA #1 : <preformatted>
Waiting for last disassociation from the db

I am not able to interpret it.

Please help me to understand what is the meaning of above message

3 Comments

Hi All

I am facing the below error when I am trying to connect to my DB2 DB .

SQL30082N Security processing failed with reason "1" ("PASSWORD EXPIRED").
SQLSTATE=08001

This User which I am using is a new user created by root to-day.

I am suspecting that this error could be due to OS configuration problem not with in DB2.

Please provide inuts

DB2
db2luw

1 Comment

On SQL Server 2012, I have two different databases(DB1 and DB2). I have created views on DB2 that select on tables from DB1. I have created a user on both databases that I want to be able to select on the views in DB2 but not select on the underlying tables on DB1. When I select on the view as the new user, I get a error saying I can't get access to the underlying table. The new user has connect access to DB1 and select on the views on DB2 (has select under Securables). Nothing else is set (under owned Schemas or Membership). Both databases have the same owner (SID same on both). I have tried doing the alter database DB1 set DB_Chaining on and granting the user select and/or connect but I still get the error.

7 Comments

Question by

george george

2018-08-22

AS400 Storage

I need to get guidance on proper storage of an as400. I believe it is running system 38 (or 36).

Regards, George

16 Comments

Question by

Jim Youmans

2018-08-21Solved

DB2 ALTER BUFFERSPACE question

DB10.5 LUW on Windows 2012

I have enabled a DDL audit using events on my production database. The main purpose was to be able to see what and who might be making changes. Looking at the activity, I see lots and logs of these commands.

ALTER BUFFERPOOL TABLESPACE1 IMMEDIATE SIZE 16162 AUTOMATIC /* db2stmm */

Looking back 30 days I am seeing this ran for different tablespaces from 300 - 700 times per day.

The server is running Windows 2012 R2 with 32 GB of memory.

From what I have read the BUFFERPOOL is the cache memory used by a given TABLESPACE, is this correct? Should it be doing this so often? If the answer is no, what do you recommend?

Thank you!

Jim

2 Comments

I have a piece of code that needs to be converted from DB2 to Oracle.
I'm trying to return all queues & sub queues that fall under the parent queue. Each sub queue can have a sub queue.
It runs fine on DB2 but getting an error message ORA-00904: invalid identifier

This is a sample portion in DB2

select * FROM OPENQUERY(DB2,'
WITH RPL(LEVEL, PARENT_QUEUE_ID, Queue_ID, NM)
AS
(SELECT 1
, ROOT.PARENT_QUEUE_ID
, ROOT.QUEUE_ID
, ROOT.NM
FROM schema.tbl_queue ROOT
WHERE ROOT.NM IN(''Auto Current'',''Auto PD'',''Auto Repo'' )
UNION ALL
SELECT PARENT.LEVEL + 1
, CHILD.PARENT_QUEUE_ID
, CHILD.QUEUE_ID
, CHILD.NM
FROM RPL PARENT, schema.tbl_queue CHILD
WHERE PARENT.QUEUE_ID = CHILD.PARENT_QUEUE_ID
)
select * from RPL
')

10 Comments

DB2

Related Topics

DB2

Related Topics