IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

How to load to DB2 with column level conditions
IBM Mainframe Forums -> DB2
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
Chitti

New User


Joined: 07 Dec 2023
Posts: 2
Location: India
PostPosted: Tue Dec 12, 2023 3:44 pm
 Reply with quote
we need to load to DB2 table with row level restrictions.

Example: I have 10 lakhs rows, I want to skip loading if PRODUCT_ID is '01234'

Is there any DB2 UTILITY commands to restrict the rows? if so please let me know the SYNTAX. If not, how to approach this problem?
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2023
Location: USA
PostPosted: Tue Dec 12, 2023 7:53 pm
 Reply with quote
I would filter out the unneeded records from the input dataset using any utility at your preference (like SORT, FILEAID, any other).

Then just load the rest of data to DB2 in full.

P.S.
What is "10 lakhs rows"? I have not a minor idea...
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2023
Location: USA
PostPosted: Tue Dec 12, 2023 9:27 pm
 Reply with quote
Chitti wrote:
we need to load to DB2 table with row level restrictions.

Example: I have 10 lakhs rows, I want to skip loading if PRODUCT_ID is '01234'

Is there any DB2 UTILITY commands to restrict the rows? if so please let me know the SYNTAX. If not, how to approach this problem?

As a Christmas gift:
Code:
//*===========================================
//*   Filter out unneeded records
//*===========================================
//FILTER   EXEC PGM=SORT
//SYSOUT   DD  SYSOUT=*
//SYMNAMES DD  *
PRODUCT_ID,position,5,CH    <-- define the field in input data
//*
//SORTIN   DD  DISP=SHR,DSN=input.data.to.load
//SORTOUT  DD  DISP=(NEW,PASS),LIKE=input.data.to.load,
//             DSN=&&FILTERED  <-- to pass to the following LOAD step
//SYSIN    DD  *
 OMIT COND=(PRODUCT_ID,EQ,C'01234')
 SORT FIELDS=COPY
 END
//*===========================================
//*   LOAD utility step
//*===========================================
//DB2LOAD  EXEC PGM=DSNUTILB,COND=(0,NE)
// . . . . . . . . . .
//SYSREC   DD  DISP=(OLD,DELETE),DSN=&&FILTERED
// . . . . . . . . .
Back to top
View user's profile Send private message
Pete Wilson

Active Member


Joined: 31 Dec 2009
Posts: 582
Location: London
PostPosted: Wed Dec 13, 2023 1:52 pm
 Reply with quote
In computing the western numbers should always be used to avoid confusion, but here's examples of the Indian counting system

5,00,000 Five lakh = 500,000

12,34,56,789 Twelve crore = 123,456,789

17,00,00,00,000 Seventeen arab = 17,000,000,000

6,78,90,00,00,00,00,000 Six padma = 6,789,000,000,000,000
Back to top
View user's profile Send private message
Chitti

New User


Joined: 07 Dec 2023
Posts: 2
Location: India
PostPosted: Wed Dec 13, 2023 3:24 pm
 Reply with quote
Hello @sergeyken .. Thanks for your sort card and Christmas gift icon_razz.gif , I was looking more of a DB2 LOAD utility though..

Do you/ anyone have idea on this using DB2 LOAD utility card?
Back to top
View user's profile Send private message
Pete Wilson

Active Member


Joined: 31 Dec 2009
Posts: 582
Location: London
PostPosted: Wed Dec 13, 2023 6:06 pm
 Reply with quote
This may help you, I'm not sure as I'm not a DBA:

www.ibm.com/docs/en/db2-for-zos/12?topic=load-loading-data-by-using-cross-loader-function

The LOAD utility can directly load the output of a dynamic SQL SELECT statement into a table. The dynamic SQL statement can be executed on data at a local server or at any remote server that complies with DRDA. This functionality is called the Db2 family cross-loader function.
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2023
Location: USA
PostPosted: Wed Dec 13, 2023 6:23 pm
 Reply with quote
Chitti wrote:
Hello @sergeyken .. Thanks for your sort card and Christmas gift icon_razz.gif , I was looking more of a DB2 LOAD utility though..

Do you/ anyone have idea on this using DB2 LOAD utility card?

AFAIK the LOAD utility has no option to filter data from input SYSREC DD.

There is LOAD ... IGNORE WHEN ... option, but only in the following forms:
Quote:
WHEN - Specifies that records that do not satisfy the WHEN clause are ignored.
    - PART Specifies that records that do not satisfy any partition being loaded are ignored.
    - CONV Specifies that records that cause a conversion error are ignored.
    - VALPROC Specifies that records that fail a validation procedure are ignored.
    - IDERROR Specifies that records that have an identity column value that is out of range are ignored.
    - DUPKEY Specifies that records that cause a duplicate key error are ignored.

It seems these options are not what you need, though I did not try using any of them.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 

Similar Topics
Topic Forum Replies
No new posts Replacing 'YYMMDD' with date, varying... SYNCSORT 3
No new posts Load new table with Old unload - DB2 DB2 6
No new posts RC query -Time column CA Products 3
No new posts REASON 00D70014 in load utility DB2 6
No new posts first column truncated in search result IBM Tools 13
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us