[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

DB2

IBM DB2 is a family of relational database server products developed by IBM that have been extended to support object-relational features and non-relational structures like JSON and XML. There are three main products in the DB2 family: DB2 for Linux, UNIX and Windows (informally known as DB2 LUW), DB2 for z/OS (mainframe), and DB2 for i (formerly OS/400), plus a version for IBM's Virtual Storage Extended (VSE) operating system.

Share tech news, updates, or what's on your mind.

Sign up to Post

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
0
Fundamentals of JavaScript
LVL 12
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

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
0
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 …
0
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.
0
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
0
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
0
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
0
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"
0
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
0
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.
0
Build an E-Commerce Site with Angular 5
LVL 12
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

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!!!
0
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.
0
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
0
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.
0
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
0
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
')
0
Hi Expert(s),

Is there a way to write Data (eventually with SQL)  from the AS400-DB2 to a MySQL-DB or Oracle?
0
Has anyone tried to use the DB@BulkCopy command within VBA?  I'm trying to get some coding to work that I found on IBM's site; however, the code is structured for C#.  I'm trying to initialize the bulk copy of data from a table within MS Access to a table on my iSeries.  Both tables have the same column names, but I can't seem to get this working all too well.  When I tried to convert this over from C# to VB, it got very sloppy and this is what I ended up with...

Public Sub copyIntoFTMEDELIGT(ANTHONY_FTMEDELIGT As DB2Connection, FTMEDTEST As DataTable)
    Dim MEDCOPY As DB2BulkCopy = NEW DB2BulkCopy Anthony_FTMEDELIGT
    FTMEDTEST.DestinationTableName = "FTMEDELIGT"
    Try
        MEDCOPY.WriteToServer (Source)
        MEDCOPY.Close()
    Catch ex As Exception
        MessageBox.Show(ex.ToString(), "Exception")
    End Try
End Sub

Open in new window


The local table in Access is FTMEDTEST and the remote table is FTMEDELIGT within the Anthony library.

If someone could help or provide some guidance if this can even be done through a VBA module in access, I'd really appreciate it.

Thank you in advanace.
0
DB2 10.5 LUW on Windows Server 2012 R2

We have a column in all our tables called LASTTIME and it is suppose to update whenever the row is edited and act as a last modified date.  The problem is that when the system was created,, the column was set to DEFAULT CURRENT TIMESTAMP and not AS ROW CHANGE.  It has been this way for years but now they want it fixed.

So I am doing the following.

Add LASTTIME2 ROW CHANGE column
Update LASTTIME2 = LASTTIME
Drop LASTTIME column
Rename LASTTIME2 to LASTTIME
Reorg table

The issue is that some of the tables are very large and it is causing a lot of logging and I have run out of space a few times.  I know I can use the NOT LOGGED INITIALLY command to avoid that but I am not sure of the scope of the command.  It seems to say the command is only active for the first UNIT OF WORK after it is activated.  So in the above chain of commands, if I do NOT LOGGED INITIALLY as the first step will it only be good for the add ROW CHANGE column command or will it be good for all of them?

Should I put it before the UPDATE command since that is what is causing most of the logging?

The IBM documentation is not real clear to me on this.

Thanks!!

Jim
0
Why Diversity in Tech Matters
LVL 12
Why Diversity in Tech Matters

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.

IBMi / AS400  Hello Experts, I have a need to pass a URL address to the internet that contains zip codes by radius and return the results.  I have found a pretty good site that I can use to pass this URL address and the results are in a CSV format.   How do I pass the URL and save the results in a DB2 file?
0
Hi All

Greetings

I have just created a new table with instance user. But I am facing the below error while doing the select of the same table which i just created.

SQL0204N  "schemaname.table1" is an undefined name.  SQLSTATE=42704

This is the first time I am facing the issue like this.

I have also done the runstats on the table, still facing the same error.

Any inputs Please.
0
DB2 10.5 LUW on Windows 2012

I have some users that are no longer with the company and I need to drop them from the database.  I revoked all of there permissions but they still show up under the SECURITY > USERS list.

I tried DROP USER MAPPING but it gave me the error:   SQL20076N  The instance for the database is not enabled for the specified action or operation.  Reason code = "1".

Even though I have revoke all permissions for them, the Security chief wants them off the user report that we run.

Any thoughts?  Thank you!!!
0
Hi, how can I pass a comma delimited string (or just 1 value) as input parameter to DB2 stored proc? I tried this -

CREATE PROCEDURE VALID_STATUS(IN IN_DOMAIN_ID VARCHAR(4096))
    SPECIFIC VALID_STATUS
    MODIFIES SQL DATA
    NOT DETERMINISTIC
    NULL CALL
    LANGUAGE SQL EXTERNAL ACTION
    INHERIT SPECIAL REGISTERS
BEGIN
DECLARE cur1 CURSOR WITH RETURN FOR
select a.ENTITY_ID,a.ATTR_VAL from ENTITY_ATTR a where a.ENTITY_TYP='EMAIL_DOMAIN' and a.ATTR_TYP='TLS' and a.ENTITY_ID in (' || @IN_DOMAIN_ID  || ');
OPEN cur1 ;
END

;

The proc should run as follows - call VALID_STATUS('D1')  (with a single parameter) or call VALID_STATUS('D1','D2','D3','D4') (any no of parameters)
0
One of our developers is attempting to do an "Insert" into a view.  They are connecting in via ODBC.

I gave the user profile *ALL authority to the View but I am getting the error:

Message: [SQL0551] Not authorized to object VSTOWERINT in DCTEST type *FILE. Cause . . . . . :   An operation was attempted on object VSTOWERINT in DCTEST type *FILE.  This operation cannot be performed without the required authority. Recovery  . . . :   Obtain the required authority from either the security officer, the object owner, or a user that is authorized to the QIBM_DB_SECADM function. If you are not authorized to a logical file, obtain the authority to the based-on files of the logical file. Try the operation again.   Processing ended because the highlighted statement did not complete successfully

I figured I needed to give the user access to the file that the view was based on...but I gave the user *ALL access to this file too and we are still getting the same error.

Does anyone have any insight into why we would be getting this error - even though we gave the user *ALL access?

Thanks in advance
0
Hi,

Right now want to research about how vulnerability each major DB has :

1) MS SQL
2) MySQL
3) MariaDB
4) Oracle
5) DB2.

Other than NIST database : https://nvd.nist.gov/vuln/search.

What other source I can search on the number of vulnerability of each product in last 3 years and the result make sense ?  the source has to be trust able !
0

DB2

IBM DB2 is a family of relational database server products developed by IBM that have been extended to support object-relational features and non-relational structures like JSON and XML. There are three main products in the DB2 family: DB2 for Linux, UNIX and Windows (informally known as DB2 LUW), DB2 for z/OS (mainframe), and DB2 for i (formerly OS/400), plus a version for IBM's Virtual Storage Extended (VSE) operating system.

Top Experts In
DB2
<
Monthly
>