DB2

5K

Solutions

4

Content

i
The collection of articles, videos, and posts on this topic.

3K

Contributors

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

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
5 Comments
Introduction to R
LVL 13
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.

Enroll Today!
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
8 Comments
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
7 Comments
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
2 Comments
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
4 Comments
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
1 Comment
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
1 Comment
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
16 Comments
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
3 Comments
How to Migrate the Image which is stored as BLOB in My Sql  to DB2 BLOB .
0
6 Comments
OWASP: Forgery and Phishing
LVL 13
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

Start Today!
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
3 Comments
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
3 Comments
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
6 Comments
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
2 Comments
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
4 Comments
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
26 Comments
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
13 Comments
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
2 Comments
I have an iSeries ODBC connection that I am using to send data from SQL Server to an iSeries. I can do a DELETE on a table, but I really need to do a TRUNCATE.

However, when I do a TRUNCATE, I get this error:

OLE DB provider "IBMDASQL" for linked server "AS400DV" returned message "SQL0104: Token TRUNCATE was not valid. Valid tokens: ( END GET SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER.

Open in new window

How can I go about doing a TRUNCATE on the iSeries database table?
0
8 Comments
Bootstrap 4: Exploring New Features
LVL 13
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

Learn More!
DB2 10.5 on Windows Server 2012 R2

In my current environment, each schema is set up in their own table-spaces (Data, Index, LOB).  So I have 20 schemas and 60 table-spaces.  The total size of the database is about 650 GB.  

So here is my question.  Does having all the schemas separated into their own table-spaces (x3) really help anything?  All the data is on the SAN so it is not like each schema is on it's own LUNs or anything.  And all the data space is SSD.

Could all these table-spaces slow down my backups?  I do weekly full backup with 5 Deltas and 1 Incremental.  So starting on Saturday it goes:  

FULL, DELTA,DELTA, DELTA, INCREMENTAL, DELTA, DELTA

All of the backups are 300+ GB and are taking between 4 and 8 hours depending on the phase of the moon (or so it seems).  I know part of this is due to having a lot of BLOB data in the database (photos mostly) but could the many table-spaces be part of it as well?  Not sure why it takes so long for the backups to run.

Any thoughts would be very welcomed.  Thank you!!

Jim
0
4 Comments
After 25 years working as a developer and DBA using DB2 for the midrange (IBM i, AS/400), my new job for the last 2 years has been DBA for DB2 on z/OS. It’s been a significant adjustment, but I’m getting the hang of it.

As I navigate DB2 on z/OS, there are a few aspects of DB2 on IBM i that I really miss. I’m hoping some of you mainframe DB2 experts can guide me in getting this kind of functionality on the mainframe. (We’re currently using DB2 version 10, but we’re upgrading to version 11 later this month.)

Are some of these things coming in a future release, or is there another product I need?

Things I miss about DB2 for i:

- Visual Explain in the native GUI (IBM i Access)
  ==> including the (largely-automated) Index Advisor
  ==> makes query-tuning MUCH easier and intuitive

- constant automatic statistics gathering
  ==> completely eliminates the need for manual gathering of statistics
  ==> never need to worry if the statistics are out of date

- automatic storage management and tablespace management
  ==> the concept of a tablespace is largely irrelevant on DB2 for i
  ==> significantly reduces the need for manual work

- more extensive query rewrite functionality
  ==> makes query-writing significantly more "forgiving" since it'll just rewrite my query behind the scenes to make it run faster

- EXCEPTION JOIN
  ==> I rarely use it, but it's really elegant and effective when I do

- "OFFSET n ROWS" clause in SELECT
  ==> Similarly, I …
0
3 Comments
I am getting this DB2 Sql error updating
> update Test.sls77_01 set uttms = 'AA'

SQL State: 38501
Vendor Code: -443
Message: [CEE9901] Application error.  RNX1216 unmonitored by CXT001U at statement 2100000001, instruction X'0000'. Cause . . . . . :   The application ended abnormally because an exception occurred and was n ot handled.  The name of the program to which the unhandled exception is sent is CXT001U CXT001U CXT001U. The program was stopped at the high-level language statement number(s) 2100000001 at the time the message was sent.  If more than one statement number is shown, the program is an optimized ILE program.  Optimization does not allow a single statement number to be determined.  If *N is shown as a value, it means the real value was not available. Recovery  . . . :   See the low level messages previously listed to locate the cause of the exception.  Correct any errors, and then try the request again.
0
3 Comments
Hi,

iSeries: I have a screen (pgm a) which calls a window (pgm b) then it call a second window for a search (pgm c) .  When pgm c returns to pgm b, the backgroup and window lines are gone. I've included screen prints.  

Any help is much appreciated.
Lynn

window-and-back-ground-disappear.docx
0
7 Comments
During the upgrade from DB2 9 to DB2 10 on z/OS, the previous (now retired) DBA converted all tablespaces from "simple" to "universal". How can I determine if they are partition-by-range or partition-by-growth?

Using RC/Query in CA/Tools from Computer Associates, I was able to reverse-engineer the CREATE TABLESPACE statement, but it's not obvious from the code which type of tablespace this is.

CREATE TABLESPACE SNF101
       IN DNF1
       USING STOGROUP GNF2
           PRIQTY 48
           SECQTY 48
           ERASE NO
       BUFFERPOOL BP1
       CLOSE NO
       LOCKMAX SYSTEM
       SEGSIZE 4
       FREEPAGE 0
       PCTFREE 5
       GBPCACHE CHANGED
       DEFINE YES
       LOGGED
       TRACKMOD YES
       COMPRESS NO
       LOCKSIZE ANY
       MAXROWS 255
       CCSID EBCDIC
;

Open in new window


Given that CREATE TABLE statement, how can I determine if this is partition-by-range or partition-by-growth?

Thanks!

Dave
0
3 Comments
I am trying to create an SSIS package that does INSERTs in to a database on an iSeries. If I set it up as an OLE DB connection, I'm successful. But, the data transfer is PAINFULLY slow. After a bit of research, it was suggested that using the ODBC connection should make the data transfers go much faster.

Anyway, I set up a new ODBC connection to the iSeries, and I receive a "Test Connection Succeeded" message when I click "Test Connection".

When I create the ODBC Destination task in the Data Flow and try to select the name of the table from the "Name of the table or the view" drop-down, I get this:

SSIS Error
Any help would be appreciated.

SSIS Version 12.0.2430.0
0
5 Comments

DB2

5K

Solutions

4

Content

i
The collection of articles, videos, and posts on this topic.

3K

Contributors

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
>
Hall of FameAnnual Expert Awards