|
View previous topic :: View next topic
|
| Author |
Message |
Chitti
New User
Joined: 07 Dec 2023
Posts: 2
Location: India
|
|
|
|
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 |
|
 |
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2218
Location: USA
|
|
|
|
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 |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2218
Location: USA
|
|
|
|
| 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 |
|
|
Pete Wilson
Active Member
Joined: 31 Dec 2009
Posts: 593
Location: London
|
|
|
|
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 |
|
|
Chitti
New User
Joined: 07 Dec 2023
Posts: 2
Location: India
|
|
|
|
Hello @sergeyken .. Thanks for your sort card and Christmas gift  , 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 |
|
|
Pete Wilson
Active Member
Joined: 31 Dec 2009
Posts: 593
Location: London
|
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2218
Location: USA
|
|
|
|
| Chitti wrote: |
Hello @sergeyken .. Thanks for your sort card and Christmas gift , 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 |
|
|
|
|
|
