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 9.7 and 10.5 on Windows 2012 Server
I am trying to restore a 9.7 database to a new server with new directories that is running 10.5 and am about to pull my hair out.  I have read the IBM references about restore with redirect and they do not make much sense to me and of course none have good examples for Windows.  

Here is the example given
   
db2 restore db sample redirect without prompting
   SQL1277W A redirected restore operation is being performed. 
   During a table space restore, only table spaces being restored can 
   have their paths reconfigured. During a database restore, storage 
   group storage paths and DMS table space containers can be reconfigured. 

   DB20000I The RESTORE DATABASE command completed successfully.

   db2 set tablespace containers for 2 using (path 'userspace1.0', path    
   'userspace1.1')
   DB20000I The SET TABLESPACE CONTAINERS command completed successfully.

   db2 restore db sample continue
   DB20000I The RESTORE DATABASE command completed successfully.

Open in new window

Nowhere does this specify the backup file for the database and it does not explain what the set tablespace for 2 means?  Shouldn't I have to give it the tablespace names?  
On the server it is running on now the data is on D:\db2\data\... and the logs are on L:\db2\logs.
On the new server I want them all on the X: drive.
Then I need to run the upgrade for 10.5.
This is a small database and not used much but it is killing me to get this moved over.
Can anyone point me at an example of how to do this (and please don't reference IBM knowledge pages, I have read them and they are not helpful).

Thank you!!!
0
CompTIA Security+
LVL 20
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

We are running DB2 LUW v9.7 on Windows.   I am struggling to understand how the "PUBLIC" group access works.    We Grant Select access to PUBLIC on most of our tables but how does that grant a Domain User access?    Is there a specific AD (active directory) group that the user has to be a member of????
0
DB2 10.5 on Windows Server 2012

I need to write a stored proc to search a customer database on multiple, optional parameters.  Lets say firstname, lastname, cust_num.  So here is what I have..

CREATE PROCEDURE DBAUtil.Jim_Search(
     IN p_first_name VARCHAR(50),
     IN p_last_name VARCHAR (50),
     IN p_cust_num INTEGER)
SPECIFIC  DBAUtil.Jim_Search
LANGUAGE SQL RESULT SETS 1

BEGIN

DECLARE v_dynamicSql varchar(5000);
DECLARE c_search CURSOR WITH RETURN FOR v_dynamicSql;

SET v_dynamicSql = 
'SELECT * FROM DBAUtil.CUSTOMER WHERE 1=1 ';

IF p_first_name IS NOT NULL THEN
    SET v_dynamicSql = v_dynamicSql || ' AND UPPER(FIRST_NAME) = UPPER(' || p_first_name || ')';
END IF ;   
     
 IF p_last_name IS NOT NULL THEN
     SET v_dynamicSql = v_dynamicSql || ' AND UPPER(LAST_NAME) = UPPER(' || p_last_name || ')'  ;  
END IF;

IF p_cust_num IS NOT NULL THEN
     SET v_dynamicSql = v_dynamicSql || ' AND CUST_NUM = ' || cust_num || ' ';
END IF;

SET v_dynamicSql = v_dynamicSql || ' ORDER BY LAST_NAME, FIRST_NAME';
 

PREPARE v_dynamicSql FROM v_dynamicSql;
open c_search;

END;

Open in new window

It compiles but when I run it like so
CALL DBAUtil.Jim_Search (NULL,'YOUMANS',123)

Open in new window

I get this
Category	Timestamp	Duration	Message	Line	Position
Error	12/27/2019 10:53:15 AM	0:00:00.020	<link> - DB2 Database Error: ERROR [42703] [IBM][DB2/NT64] SQL0206N  "YOUMANS" is not valid in the context where it is used.	11	0

Open in new window


So I know my sql string is getting messed up but not sure how.  Is there a way to print the v_dynamicSql string that it is trying to run?  I have tried a couple of ways but no luck.

Is there a better way to do this?

Thanks!!!
0
DB2 on Windows Server 2012

We recently started getting this error message.
 CLI0129E  An attempt to allocate a handle failed because there are no more handles to allocate. 

Open in new window

From what I have read, this is an issue with .NET Entity Framework not releasing connections to the database.  The developers are searching the application code to see if they can find the issue but in the mean time, is there anything I can do on the DB side to help with this?

I read this:  https://www.ibm.com/support/pages/cli0129e-no-more-handles  
and this: https://www.ibm.com/support/pages/how-increase-number-statment-handles-available-your-application

but not sure I understand what they are saying.  This is the first exposure I have had to DB2 handles and not sure I understand them as well as I could.  Any insight would be much appreciated.
0
DB2 10.5 on Windows.
I did normal windows patching and reboot this morning and now I am getting this error in the DIAG Log.

2019-11-07-07.09.10.427000-360 I6616701F603         LEVEL: Severe
PID     : 1768                 TID : 9092           PROC : db2syscs.exe
INSTANCE: DB2                  NODE : 000
APPHDL  : 0-15306
HOSTNAME: DB03
EDUID   : 9092                 EDUNAME: db2agent (idle) 0
FUNCTION: DB2 UDB, base sys utilities, sqeAppServices::InterruptAppByIndex, probe:2861
MESSAGE : ZRC=0x80050810=-2147153904=SQLE_RC_INVALID_AGENT_INDEX
          "Agent index is invalid"
DATA #1 : String, 55 bytes
Ignoring user interrupt that targets system application
DATA #2 : unsigned integer, 2 bytes

Open in new window


When I google it I don't get any results.  Anyone have any thoughts?
0
I need to calculate number of days, adjust it, and then use it to compare on embedded SQL statement. Details attached.  

Thank You in Advance!
Lynn days-calc-with-caveat.docx
0
Hi, I'm trying to retrieve order number, status, customer#, customer name from our order master and customer master when the customer is on our preferred customer file.  This runs but returns nothing when it should.  Where am I going wrong?   Note: CXSPRF is a preferred level then multiple customer are assigned to it.  So I want all custest1 level where the customer number from the order master is assigned to it and active.

Select a.CHONUM,a.CHOSTS,a.CHOCUS,b.CCNAME                              
from CORDER a, CUSTMST b                                        
where a.CHOCUS = b.CCNUM                                        
  and (a.CHODTE between 20190101 and 20190931)                  
  and EXISTS                                                    
    (SELECT * FROM CCXCPRF c WHERE ((c.CXSPRF = 'CUSTEST1' AND  
                                                                        a.CHOCUS = c.CXSCUS)  AND  
                                                                        c.CXSSTS = 'A'))          


Thank you in advance!!
Lynn
0
Hi,
   We are on DB2 UDB 9.7
I have a simple sql like below.
The alias names are static and so want to know if these can be made dynamic, to print the date at the run time.
Please help

SELECT SUM(CASE WHEN START_DT = current_date - 2 days THEN 1 ELSE 0 END ) AS as_of_09212019
             ,SUM(CASE WHEN START_DT = current_date - 1 day THEN 1 ELSE 0 END ) AS as_of_09222019
            ,SUM(CASE WHEN START_DT = current_date  THEN 1 ELSE 0 END ) AS as_of_09232019
    FROM TBL_VISION
WHERE START_DT BETWEEN CURRENT_DATE - 2 DAYS AND CURRENT_DATE

Open in new window

0
DB2 10.5 on Windows Server 2012.

I am trying to do a simple backup and restore of a small database from one server to another and I can't get it to work.  Here is what I am doing.

Server 1 has a small database called TOOLSDB I run this.
DB2 backup db TOOLSDB ONLINE to X:\DBBackups include logs

Open in new window

And I get this a backup completed with timestamp xxx and a backup file in the X:\DBBackups folder

I copy the backup file to Server2 and run
db2 restore db TOOLSDB FROM X:\DBBackups

Open in new window

It completes with RESTORE DATABASE comm completeld successfully, so then I run
db2 rollforward db TOOLSDB to end of backup

Open in new window

And I get missing log file S0000427.LOG error.
Why??  when you do a backup with include logs shouldn't everything you need be in the backup file?  What am I doing wrong?  

Jim
0
What will happen in the following situation, I can't force this nor test this.

In a process a recordset is selected with SQL with a selection e.g. WHERE CODE = 1
(The result can contain a few hundred records.)
each record will be processed (within 15-30 seconds) and flagged as "CODE = 2"

Now a second job (same program) is started.
This program will also select a recordset with SQL with a selection e.g. WHERE CODE = 1.

What will happen when:
Job 1 select a records with code=1
(e.g result 250 records)

Job 2 select a records with code=1
(e.g result 240 records, 10 already processd by job 1)

Job 1 change value of a record to CODE = 2.
Is this record still in the recordset from Job 2?
0
Become a Certified Penetration Testing Engineer
LVL 20
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

I need to pull dates from an as400 table. I would normally expect a CYYMMDD format. This is something different, and I cannot figure out the format.
Dates are expressed as
800101
800102
etc.
In columns with the title MDY, these are expressed as
10180
10280
etc.
WDJUL, which I assume stands for Weekday, Julian is expressed as
80001
80002
etc.
Anyone have an idea what they are doing here.
0
Hi All,

I have the below query, where I'm trying to use a field I created in a table, which is a combination of values from 3 other columns.  I tested the logic of the combination of values and that works fine.  When I go to use that new field in a join to another like field in another table, I get an SQL0802- Data conversion or data mapping error...

Any help on getting this corrected, will be greatly appreciated.  

SELECT  INPOL100.CMPNO AS COMPANY   
	, INPOL100.PSTAT AS STATUS   
	, INPOL100.PURCH AS "PO NUM"   
	, INPOL300."LINE#" AS "PO LINE NUM"   
	, INPOL100.PODDT AS "PO DATE"   
	, INPOL300.PORDT AS "LAST RECEIPT DATE"   
	, INPOL300.SDESC AS "PO DESCRIPTION"   
	, INPOL300.PRDNO AS "PRODUCT NUMBER"   
	, INPOL300.P3OR1 AS "ORG CODE 1"   
	, INPOL300.P3OR2 AS "ORG CODE 2"   
	, INPOL300.ECOST AS "$ / UNIT"   
	, INPOL300.P3QOR AS "QUANTITY ORDERED"   
	, INPOL300.P3QAC + INPOL300.P3QOR AS QUANTITY_RECEIVED   
	, INPOL300.P3QRJ AS "QUANTITY RETURNED"   
	, INPOL300.P3QOR - (INPOL300.P3QAC + INPOL300.P3QOR) + INPOL300.P3QRJ AS OPEN_QUANITY   
	, APACP10X.ACGLA AS "GL ACCOUNT"
FROM 
	((LOGICLIB.INPOL100 INPOL100
	INNER JOIN
	LOGICLIB.INPOL300 INPOL300
	ON (INPOL100.PURCH = INPOL300.PURCH))
	INNER JOIN
	HERBERT.APACP10X APACP10X
	ON (INPOL100.PURCH = APACP10X.ACPUR))
	INNER JOIN
	RMSGL800.VALMAST VALMAST
	ON (APACP10X.ACGLA = 

Open in new window

0
I have a package that loads 300 tables from DB2 to SQL Server.  We added a column to all the DB2 tables to keep track of changes named DW_LASTTIME.  
My question is, each table has a data flow task with the source table having a variable like this to pull the data.

"SELECT * FROM ABC WHERE LASTTIME >= " +  @[User::strAdjLastRunTime] +" WITH ur"

Open in new window


So I need to update all 300 to make it say

"SELECT * FROM ABC WHERE DW_LASTTIME >= " +  @[User::strAdjLastRunTime] +" WITH ur"

Open in new window

Is there any way to do this without having to manually open and update each dataflow?  Like in a text editor or something?  I know SSIS packages are just XML files but I was told if you mess with the actually file, it can corrupt it.

Thanks!

Jim
0
Experts,
Our software contains ratesheets. They are displayed in the software as traditional grid with an X axis, Y axis and all the values.
I am trying to write an select statement so I can export them.
I expected to find a single table and would need to PIVOT.

Instead, I find the data in 3 tables (Columns, Rows, and Cells)

RATE_COL
Sheet_ID
Col_id   (typically 10 columns)
Col_Label

RATE_ROW
Sheet_ID
Row_ID  (typically 100+ rows)
Row_Label

RATE_CELL
Sheet_ID
Row_ID
Col_ID
Cell_Value


Pretty sure I need to Pivot RATE_COL, but unsure how to incorporate the rest of the data
I am lost.. and need some help

I am querying against a DB2 database
0
My daily DB2 backups have gone from a couple of hours to 22 hours.  Here are the stats from my last backup.
Parallelism       = 5
Number of buffers = 10
Buffer size       = 3149824 (769 4kB pages)
                                                                               Compr     Retry       %   
BM#    Total      I/O      Compr     MsgQ      WaitQ      Buffers   MBytes    MBytes    MBytes     Retry 
---  --------  --------  --------  --------  --------    --------  --------  --------  --------  --------
000  82411.97   1657.43    296.71      0.00  78085.25        1899    174805     10502         5       0.1
001  82404.48   2133.06  13840.11      0.10      0.18      107275    380546    380340       347       0.1
002  82404.48    720.83   2260.55      0.00  72087.44        3334     57064     56920         5       0.0
003  82404.37    805.24   2539.45      0.00  69463.70        2247     77614     77537         0       0.0
004  82404.30   1864.22    285.66      0.00  77991.13        1657    178222      9018         4       0.1
---  --------  --------  --------  --------  --------    --------  --------  --------  --------  --------
TOT  412029.62   7180.80  19222.50      0.11  297627.73      116412    868252    534319       364       0.1

MC#    Total      I/O                MsgQ      WaitQ      Buffers   MBytes 
---  --------  --------            --------  --------    --------  --------
000  82411.81    294.56            82115.54      0.00      116413    349687
---  

Open in new window

0
We are upgrading our DB2 LUW environment.  Because we are moving 32bit to a 64 bit Windows, the Server names are changing.  

I have the need to provide a Script to our Desktop Support Team Containing the DB2 unCatalog \ Catalog Statements so they can push the changes to the Windows 7 and Window10 laptops in our Enterprise environment.

I know how to write the Catalog Statement scripts but I am struggling with how to evoke a DB2 Command window in the script so that the DB2 catalog statements will run.
0
How to Migrate the Image which is stored as BLOB in My Sql  to DB2 BLOB .
0
I am creating a process to delete any data > 6 months old from a large table.  It will run on the first of each month.  The amount of data I am deleting can be greater than 10,000,000 rows so I would like to delete 10,000 at a time until they are all deleted.  In SQL Server I would write something like this...

WHILE (SELECT COUNT(*) FROM TABLE_ONE WHERE LASTTIME < GETDATE()-6 MONTHS) > 0
BEGIN
     DELETE TABLE_ONE WHERE MAIN_ID IN (SELECT TOP 10000 MAIN_ID FROM TABLE_ONE WHERE LASTTIME < GETDATE()-6 MONTHS)
END;

Open in new window

How would I do similar operation in DB2?  Thanks!
0
On DB2 for z/OS version 10, an errant utility left MANY indexspaces in a status of "RW,RBDP" within a specific database. I can successfully use the REBUILD INDEXSPACE command to fix them one-by-one, but there are A LOT of them. So, I was hoping for some kind of wild-card or *ALL option, but that doesn't yet work for me.

Is there a way to do the equivalent of the following?

REBUILD INDEXSPACE (MYDB.*)

Thanks in advance!
Dave
0
Introduction to R
LVL 20
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Hi,

I have the following DB2 SQL update satement:

UPDATE "FLNFILTST"/"MFWOL100AM" A                                 
SET A.DUEDT = (SELECT B.NEWDT FROM "FLNFILTST"/"MFWOPUP" B WHERE  
(A.WRKNO = B.WRKNO) AND (A.CMPNO = B.CMPNO))

Open in new window


When I go to run the statement as a sample, I get the error:  "Null values not allowed in column or variable DUEDT" although I have values from the the table, MFWOPUP that want to use to update.

Any help would be appreciated.

-Anthony
0
Using DB2 for z/OS 10, I'm have a column (ADDRESS3) in a table that contains city, state, and zipcode all in one column. But, the formatting is somewhat "free-form". (see code below)

I can successfully parse the city by taking everything before the comma. Using only DB2 SQL, how would I parse out the state and zipcode?

SELECT ADDRESS3,
       TRIM(SUBSTR(ADDRESS3,1,LOCATE(',', ADDRESS3)-1)) AS CITY
  FROM MYTABLE

ADDRESS3                            CITY         
----------------------------------  -------------
GRANADA HILLS       ,CA 91344       GRANADA HILLS
SIMI VALLEY,  CA 93065              SIMI VALLEY  
BUENA PARK          ,CA 90621       BUENA PARK   
SHERMAN OAKS, CA 91423              SHERMAN OAKS 
GLENDALE            ,CA 91203-2089  GLENDALE     
VENTURA             ,CA 93002       VENTURA      
HAWTHORNE           ,CA 90250       HAWTHORNE    
PASADENA          ,CA 91185-2594    PASADENA     
BEVERLY HILLS       ,CA 90211       BEVERLY HILLS
ARCADIA             ,CA 91007       ARCADIA      
WALNUT CREEK        ,CA 94596       WALNUT CREEK 
INDEPENDANCE        ,CA 93526       INDEPENDANCE 
LOS ANGELES         ,CA 90017       LOS ANGELES  

Open in new window

Ultimately, I'd like the query results to look like this:

CITY          STATE ZIP
------------- ----- -----
GRANADA HILLS CA    91344
SIMI VALLEY   CA    93065
BUENA PARK    CA    90621
SHERMAN OAKS  CA    91423
GLENDALE      CA    91203
VENTURA       CA    93002
HAWTHORNE     CA    90250
PASADENA      CA    91185
...

Open in new window


Thanks!
0
Hi.  Format of Db2 SQL update statements over joined tables is confusing but I think I have a handle on it.  I'm hoping someone can verify my code before I make an update in my ERP system.    I'm only updating 1 record to start but even so, I want to make sure I have the statement formatted correctly before I even do that.  

Update-ITCLS-a-and-set-to-value-of-.docx  I've attached a partial output of a select statement over my joined tables:  ITEMBL a and ITMRVA b.  There is a 1 - many relationship from table b to table a joined on the ITNBR (multiple House records for each ITNBR record in table b).  Both tables include a field called ITCLS that is manually updated in table b but is not getting updated in table a.  My task is to copy value of table b to table a where the ITNBR matches, regardless of house designation and where a.ITCLS <> b.ITCLS.    (about 2000 records out of 100k or so..

I think this code might do the trick but I'm not 100% sure...  After a test I'd substitute the single itnbr for "AND a.ITNBR = b.ITNBR"

 
UPDATE amflibx.itembl as a
  SET itcls = 
    (SELECT b.itcls 
        FROM amflibx.itmrva b                                    
        JOIN amflibx.itembl a ON a.itnbr = b.itnbr              
        WHERE a.itcls <> b.itcls AND b.itnbr = 'AN02.5')
 Where exists
    (select * 
        FROM amflibx.itmrva b                                    
        JOIN amflibx.itembl a ON a.itnbr = b.itnbr              
        WHERE a.itcls <> b.itcls AND b.itnbr = 'AN02.5')  

Open in new window

0
DB2 9.7 on Windows Server 2012

My user on the database has all the permissions possible but it still can't do anything.  Here are the permissions I have.

 Permissions.PNG
But if I try to do almost anything I get a permissions error.  I try to run LIST APPLICATIONS and I get this

SQL1092N  The requested command or operation failed because the user ID does
not have the authority to perform the requested command or operation.  User
ID: "JYOUMANSSO".


Any thoughts?  I should be able to do anything with the permissions I have I thought.

Thanks.

Jim
0
We have EDI software on Windows that connects to an iSeries DB2 via a JDBC driver and transmits data from SQL Server to DB2. We want to move this to SSIS.

In the EDI software, NULL values and invalid DB2 characters get passed without error.

However, using the "iSeries Access ODBC Driver" in SSIS, any null or invalid chars result in an error. There's a dozen jobs and dozens of data columns that need to be transferred, and I can't possibly account for every single possible character that might cause DB2 to scream at me.

Is there any setting on the ODBC driver that will make it behave like the JDBC driver?

Thanks in advance for your help.
0
I have an SSIS job that sends data from SQL Server to a DB2 on an iSeries using ODBC. In the ODBC Destination task, the "Batch size" is set to 1000. There are 166,103 records to transfer.

When I run the job, it errors out after transferring 165,280 records (823 didn't make it over).

If I change the "Batch size" to 5000, it errors out after transferring 162,280 records (3823 didn't make it over).

If I change the "Batch size" to 10,000, it errors out after transferring 157,280 records (8823 didn't make it over).

Why is the "Batch size" affecting the transfer? It doesn't make sense.

Here are the errors:

Error: 0x384 at Add to BOSS, BOSS Destination [2]: Open Database Connectivity (ODBC) error occurred. SQLExecute returned error while inserting row 157281

Error: 0x384 at Add to BOSS, BOSS Destination [2]: Open Database Connectivity (ODBC) error occurred. state: '22018'. Native Error Code: 30200. [IBM][iSeries Access ODBC Driver]Column 2: CWBNL0107 - Converted 60 bytes, 1 errors found beginning at offset 14

Error: 0x384 at Add to BOSS, BOSS Destination [2]: Open Database Connectivity (ODBC) error occurred. state: '22018'. Native Error Code: 30019. [IBM][iSeries Access ODBC Driver]Error in assignment.

Error: 0xC0047022 at Add to BOSS, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "BOSS Destination" (2) failed with error code 0x80004005 while processing input "ODBC Destination Input" (13). The
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
>