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

effect of changed varchar column lengths on load-reuse


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Mar 04, 2011 10:45 pm
Reply with quote

don't have any dba support at our site. the site we use, the dba's are usually posting here.
so instead of asking them and waiting for them to get an answer from the board, figured I was just man-up and admit that I don't know everything and ask the question:

A cobol program SELECTs everything from a table, change names, streets, etc. with INSPECT CONVERTING,
the output of the cobol program is used in a LOAD- reuse (or replace??).
until now, we have not modified the lengths of varchar columns.

my question, if I change the length of a varchar column, what effect does it have on the db2 table?

like an idiot, I assumed (that's the idiot part), that the table was dropped, created, then loaded, but it is not.

So I am assuming (being an idiot again), that the reuse/replace parm tells db2 to replace the old row with a new row.
if each row is the same (as far as varchar lenghts are concerned, i did not forsee a problem.

some of the varchar columns are index columns, and we have been modifying the data values, so indexes are being deleted/inserted anyway.

currently with the load-reuse or replace (have to find out which)
I assume we are not doing rstats or anything else. just load-and-go.

but if we drop/create/load - that process my be faster,
but would we be required then to do rstats, etc... before --going?

but, we may have to change the varchar lengths (mostly smaller, but some larger) and I am worrying about the effect of varchar length changes on the load process -base db2 table,
as I said, we have been eating the index changing anyway.

we dump/change/load 6 tables, total about 60 million rows now in 1/2 hour. (30 minutes wall clock.)

I do not want this to become a 2 hour process (or even a 45 minute process).

So, educate me (and others - by the way) on the affects of varchar length changes on a LOAD - replace or reuse.
would it take longer to drop, create, load?

thx, have a good weekend, will read you on Monday.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Sun Mar 06, 2011 1:03 pm
Reply with quote

Hi dbz,

I think, loading a table with lot of VARCHAR columns would take more time, when compared to tables with CHAR columns, because load utility has to read the length field of the column in the input dataset for each row and then load the data. This process would take more time, after reading the length field DB2 knows how much of data it should read in the row for that column, so i think, it doesn't matter if its a big/small varchar column. Please correct me if i am wrong.

Drop/Create/Load process may take sometime because, dropping a table would trigger dropping all its dependent objects like, indexes, views, synonym and invalidates all the dependant packages. Whereas LOAD REPLACE with REUSE gives you a little performance advantage by not deleting and redefining underlying DB2 Datasets and statistics can be gathered during the load itself.

Can you tell, what is the length of the varchar columns and is the table partitioned or non-partitioned ?

Thanks,
Sushanth
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon Mar 14, 2011 8:22 pm
Reply with quote

AFAIK : load replace reuse does not work on row level

It works on pages/ segments/blocks/ tracks/cylinders / VSAM allocation.

And index-keys are gathered during load, afterwards sorted and then indexes built. So no delete/insert stuff is going on in index pages.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Mon Mar 14, 2011 8:54 pm
Reply with quote

appreciate the answers.

Using DSNUTILB here.

'LOAD DATA REPLACE LOG NO NOCOPYPEND'

EXISTING RECORDS DELETED FROM TABLESPACE

when we are dealing with tables with more than 5,000,000 large rows,
we use BMC utilities. Less than that, we use DSNUTILB.

My basic question was (due to total ignorance),
when I modify the length of VARCHAR columns, will I impact the load?
in the case of data replace, does not matter. it is a new row on in a clean table.
The fact that the table was deleted of all rows (Tablespace RECORDS),
based on my load parms, made this a no-brainer.

Will have to experiment with other parms. honestly, do not know what the resuse parm is, have not used it.
Back to top
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada

PostPosted: Mon Mar 14, 2011 9:03 pm
Reply with quote

Load REPLACE works on a table space level. It totally wipes out all data in the table space, which may contain more than 1 table.

REUSE tells DB2 to not delete and redefine the table space data sets.

When you say you are increasing the length of a VARCHAR column, do you mean that you are changing the column definition? Or is it just the contents of the column?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Mon Mar 14, 2011 9:23 pm
Reply with quote

just increaing the contents. not attempting to alter the column definition.

well, considering Load REPLACE is at a table space level,
lucky that I am in a 1 table per tablespace shop.

So, REUSE would essentially add my load data to the existing table content?

thx again, for the responses.

will be on vacation til next monday.
y'all enjoy yourself.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon Mar 14, 2011 9:53 pm
Reply with quote

no, adding rows would be RESUME.

REUSE is just about (not) freeing/re-allocating space. which is good if you're not changing stuff like CI-size, disks, striping (anything involving allocation parameters of the VSAM)
Back to top
View user's profile Send private message
haimzeevi

New User


Joined: 01 Mar 2010
Posts: 27
Location: Israel

PostPosted: Tue Mar 15, 2011 2:50 am
Reply with quote

Hi,
First, you can issue an UNLOAD from the table, using //SYSREC0 DD DUMMY just to have the //SYSPUNCH file, which is the exact description of the table. Then use this SYSPUNCH file for the LOAD (one of LOAD parameters) to have table's description and reduce overhead.
Second, you mentioned you have BMC utilities, if you can use BMC CATALOG MANAGER, you can either UNLOAD & LOAD tables via interactive panels, you can create the relevant DB2 commands, save them in a PDS, and run them later via BMC CATALOG MANAGER or SPUFFI.
That's best way I know.
If you want, send me a mail to e-addr removed and I can provide you a LOAD procedure to run under a JOB.
All the best,
Haim Zeevi
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Tue Mar 15, 2011 3:24 am
Reply with quote

Hello,

Please do not post your "real" contact info - we have had problems with spammers, recruiters and the like.

It may help several people if you post this LOAD procedure as a reply to this topic. Please use the "Code" tag.
Back to top
View user's profile Send private message
haimzeevi

New User


Joined: 01 Mar 2010
Posts: 27
Location: Israel

PostPosted: Tue Mar 15, 2011 3:35 am
Reply with quote

Thanks Dick, icon_redface.gif
I'll post the procedure about tomorrow.
Haim Zeevi
Back to top
View user's profile Send private message
haimzeevi

New User


Joined: 01 Mar 2010
Posts: 27
Location: Israel

PostPosted: Tue Mar 15, 2011 5:24 pm
Reply with quote

Hi,
I enclose both Load PROCEDURE & a sample job. Modify them to meet site's standards, as needed.
Haim Zeevi
Code:
//jobname JOB jobcard details...,NOTIFY=&SYSUID                   
// JCLLIB ORDER=(PROCLIB pds)                                                   
//*--------------------------------------------------------------               
//*           TYPE   R = REPLACE , Y = RESUME                                   
//*--------------------------------------------------------------               
//LOADDBLL  EXEC  DB2LOADN,OUT=*,UID=jobname (2nd-level-qualifer for DSN's),DB2=database name,                         
//          TB='creator.tablename',PRE=high-level-qualifer for DSN's,SUBJECT=2nd-level-qualifer for DSN's,                       
//          INFILE=input DSN,TYPE=R                             
//**************************************************           
                [b]procedure:[/b]

//*******************************************************************           
//*                                                                             
//*             PROC FOR LOAD DB2 TABLE                                         
//*                                                                             
//*  PARMS: DB2       = database name
//*         CREATOR   = CREATOR OF TABLE                                       
//*         TB        = TABLE NAME                                             
//*         PRE       = PREFIX OF environment (i.e. TEST / PROD etc.)
//*         SUBJECT   = SUBJECT OF APPLICATION
//*         INFILE    = DSN OF THE INPUT FILE                                   
//*         SORTNUM   = NUMBER OF SORT FILES ALOCATED DYNAMICLY BY DB2         
//*         SP1       = PRIMARY ALLOC FOR WORK FILES (IN TRK)                   
//*         SP2       = SECOND ALLOC FOR WORK FILES (IN TRK)                   
//*         TYPE      = TYPE OF THE LOAD:                                       
//*                   = REPLACE REUSE = 'R'                                     
//*                   = RESUME YES = 'Y'                                       
//*                   = REPLACE = 'N'                                           
//*         UID       = UTILITY  ID -  JOBNAME
//*         OUT       = CLASS FOR SYSOUT                                       
//*         LOG       = DEFAULT NO                                             
//*         SORTKEYS  = SORTING THE INDEX IN PARALLEL                           
//*         ENFORCE   = CONSTRAINTS - LOAD ENFORCE CHECK + REFERENTIAL         
//*                                   CONSTRAINTS. THE DEFAULT                 
//*                   = NO          - LOAD DO NOT ENFORCE                       
//*                                   CHECK + REFERENTIAL CONSTRAINTS           
//*                                   THE TARGET TS IS PLACED IN CHKP           
//*         N         = PART NUMBER                                             
//*         SHRLEVEL  = ACCESS DURING LOAD:                                     
//*                   = NONE - NO ACCESS                                       
//*                   = CHANGE - READ & WRITE DURING LAOD                       
//*         STORCLAS  = CLASS FOR SMS                                           
//*         FLDPOS    = FILE OF FIELDS POSITION (place here //SYSPUNCH from UNLOAD)                                 
//*         PREPOS    = PREFIX OF PARMLIB DSN
//*                                                                             
//*                                                                             
//*******************************************************************           
//*                                                                             
//DB2LOADN PROC UID=,                                                           
//         DB2=,                                                               
//         PRE=,                                                               
//         SUBJECT=,                                                           
//         INFILE=,                                                             
//         TYPE=,                                                               
//         SP1=2250,                                                           
//         SP2=750,                                                             
//         OUT=L,                                                               
//         LOG='NO',                                                           
//         N=0,                                                                 
//         SHRLEVEL='NONE',                                                     
//         ENFORCE='CONSTRAINTS',                                               
//         SORTKEYS=,                                                           
//         STORCLAS='WORK',                                                     
//         FLDPOS='DB2FLDPN',                                                   
//         PREPOS=high-level-qualifier of PARMLIB,                                                         
//         TB=                                                                 
//*                                                                             
//CCARD1 EXEC PGM=CCARD,                                                       
// PARM='LOG &LOG SHRLEVEL &SHRLEVEL ENFORCE &ENFORCE &SORTKEYS'               
//STEPLIB  DD   DSN=DB2_LOADLIB,DISP=SHR                                         
//C        DD   DSN=&&INCARD1,DISP=(,PASS),UNIT=DASD,SPACE=(TRK,1)             
//*                                                                             
//*CCARD11 EXEC PGM=CCARD,                                                     
//* PARM='SORTDEVT DASD SORTNUM &SORTNUM'                                       
//*STEPLIB  DD   DSN=DB2_LOADLIB,DISP=SHR                                       
//*C        DD   DSN=&&INCARD1,DISP=(MOD,PASS),UNIT=DASD,SPACE=(TRK,1)         
//*                                                                             
//CCARD12 EXEC PGM=CCARD,                                                       
// PARM='INTO TABLE &TB '                                                       
//STEPLIB  DD   DSN=DB2_LOADLIB,DISP=SHR                                         
//C        DD   DSN=&&INCARD1,DISP=(MOD,PASS),UNIT=DASD,SPACE=(TRK,1)           
//*                                                                             
//IFRC0    IF (&N>0) THEN                                                       
//CCARD2 EXEC PGM=CCARD,                                                       
// PARM='PART &N '                                                             
//STEPLIB  DD   DSN=DB2_LOADLIB,DISP=SHR                                         
//C        DD   DSN=&&INCARD1,DISP=(MOD,PASS),UNIT=DASD,SPACE=(TRK,1)           
//*                                                                             
//*********************************************************                     
//LOADP    EXEC PGM=DSNUTILB,PARM='&DB2,&UID'                                   
//STEPLIB  DD DISP=SHR,DSN=DB2_Loadlib
//SYSUT1  DD DSN=&PRE..&SUBJECT..&UID..UT1,DISP=MOD,                           
//           SPACE=(TRK,(&SP1,&SP2),RLSE),STORCLAS=&STORCLAS                   
//SW01WK01 DD DSN=&PRE..&SUBJECT..&UID..SW01WK01,DISP=MOD,                     
//            SPACE=(TRK,(&SP1,&SP2),RLSE),STORCLAS=&STORCLAS                   
//SW01WK02 DD DSN=&PRE..&SUBJECT..&UID..SW01WK02,DISP=MOD,                     
//            SPACE=(TRK,(&SP1,&SP2),RLSE),STORCLAS=&STORCLAS                   
//SW02WK01 DD DSN=&PRE..&SUBJECT..&UID..SW02WK01,DISP=MOD,                     
//            SPACE=(TRK,(&SP1,&SP2),RLSE),STORCLAS=&STORCLAS                   
//SW02WK02 DD DSN=&PRE..&SUBJECT..&UID..SW02WK02,DISP=MOD,                     
//            SPACE=(TRK,(&SP1,&SP2),RLSE),STORCLAS=&STORCLAS                   
//SW03WK01 DD DSN=&PRE..&SUBJECT..&UID..SW03WK01,DISP=MOD,                     
//            SPACE=(TRK,(&SP1,&SP2),RLSE),STORCLAS=&STORCLAS                   
//SW03WK02 DD DSN=&PRE..&SUBJECT..&UID..SW03WK02,DISP=MOD,                     
//            SPACE=(TRK,(&SP1,&SP2),RLSE),STORCLAS=&STORCLAS                   
//SW04WK04 DD DSN=&PRE..&SUBJECT..&UID..SW04WK01,DISP=MOD,                     
//            SPACE=(TRK,(&SP1,&SP2),RLSE),STORCLAS=&STORCLAS                   
//SW04WK02 DD DSN=&PRE..&SUBJECT..&UID..SW04WK02,DISP=MOD,                     
//            SPACE=(TRK,(&SP1,&SP2),RLSE),STORCLAS=&STORCLAS                   
//SW05WK04 DD DSN=&PRE..&SUBJECT..&UID..SW05WK01,DISP=MOD,                     
//            SPACE=(TRK,(&SP1,&SP2),RLSE),STORCLAS=&STORCLAS                   
//SW05WK02 DD DSN=&PRE..&SUBJECT..&UID..SW05WK02,DISP=MOD,                     
//            SPACE=(TRK,(&SP1,&SP2),RLSE),STORCLAS=&STORCLAS                   
//SORTWK1 DD DSN=&PRE..&SUBJECT..&UID..WK1,DISP=MOD,                           
//           SPACE=(TRK,(&SP1,&SP2),RLSE),STORCLAS=&STORCLAS                   
//SORTWK2 DD DSN=&PRE..&SUBJECT..&UID..WK2,DISP=MOD,                           
//           SPACE=(TRK,(&SP1,&SP2),RLSE),STORCLAS=&STORCLAS                   
//SORTWK3 DD DSN=&PRE..&SUBJECT..&UID..WK3,DISP=MOD,                           
//           SPACE=(TRK,(&SP1,&SP2),RLSE),STORCLAS=&STORCLAS                   
//SORTWK4 DD DSN=&PRE..&SUBJECT..&UID..WK4,DISP=MOD,                           
//           SPACE=(TRK,(&SP1,&SP2),RLSE),STORCLAS=&STORCLAS                   
//SORTOUT DD DSN=&PRE..&SUBJECT..&UID..OUT,DISP=MOD,                           
//           SPACE=(TRK,(&SP1,&SP2),RLSE),STORCLAS=&STORCLAS                   
//SYSMAP  DD DSN=&PRE..&SUBJECT..&UID..MAP,DISP=MOD,                           
//           SPACE=(TRK,(&SP1,&SP2),RLSE),STORCLAS=&STORCLAS                   
//SYSERR  DD DSN=&PRE..&SUBJECT..&UID..ERR,DISP=MOD,                           
//           SPACE=(TRK,(&SP1,&SP2),RLSE),STORCLAS=&STORCLAS                   
//SYSDISC DD DSN=&PRE..&SUBJECT..&UID..DISC,DISP=MOD,                           
//           SPACE=(TRK,(&SP1,&SP2),RLSE),STORCLAS=&STORCLAS                   
//SYSREC00  DD  DISP=SHR,DSN=&INFILE                                           
//UTPRINT DD SYSOUT=&OUT                                                       
//SYSPRINT DD SYSOUT=&OUT                                                       
//SYSIN    DD DSN=SYS3.DB.TS0R0.PARMLIB(DB2LOAD1),DISP=SHR                     
//         DD DSN=SYS3.DB.TS0R0.PARMLIB(DB2COPY&TYPE),DISP=SHR                 
//         DD DSN=&&INCARD1,DISP=(SHR,DELETE,DELETE)                           
//         DD DSN=SYS3.DB.TS0R0.PARMLIB(DB2TYPE&TYPE),DISP=SHR                 
//         DD DSN=&PREPOS..PARMLIB(&FLDPOS),DISP=SHR                           
//ELSEIF   ELSE                                                                 
//*********************************************************                     
//LOAD     EXEC PGM=DSNUTILB,PARM='&DB2,&UID'                                   
//STEPLIB  DD DISP=SHR,DSN=DB2_Loadlib               
//SYSUT1  DD DSN=&PRE..&SUBJECT..&UID..UT1,DISP=MOD,                           
//           SPACE=(TRK,(&SP1,&SP2),RLSE),STORCLAS=&STORCLAS                   
//SW01WK01 DD DSN=&PRE..&SUBJECT..&UID..SW01WK01,DISP=MOD,                     
//            SPACE=(TRK,(&SP1,&SP2),RLSE),STORCLAS=&STORCLAS                   
//SW01WK02 DD DSN=&PRE..&SUBJECT..&UID..SW01WK02,DISP=MOD,                     
//            SPACE=(TRK,(&SP1,&SP2),RLSE),STORCLAS=&STORCLAS                   
//SW02WK01 DD DSN=&PRE..&SUBJECT..&UID..SW02WK01,DISP=MOD,                     
//            SPACE=(TRK,(&SP1,&SP2),RLSE),STORCLAS=&STORCLAS                   
//SW02WK02 DD DSN=&PRE..&SUBJECT..&UID..SW02WK02,DISP=MOD,                     
//           SPACE=(TRK,(&SP1,&SP2),RLSE),STORCLAS=&STORCLAS                   
//SW03WK01 DD DSN=&PRE..&SUBJECT..&UID..SW03WK01,DISP=MOD,                     
//            SPACE=(TRK,(&SP1,&SP2),RLSE),STORCLAS=&STORCLAS                   
//SW03WK02 DD DSN=&PRE..&SUBJECT..&UID..SW03WK02,DISP=MOD,                     
//            SPACE=(TRK,(&SP1,&SP2),RLSE),STORCLAS=&STORCLAS                   
//SORTWK1 DD DSN=&PRE..&SUBJECT..&UID..WK1,DISP=MOD,                           
//           SPACE=(TRK,(&SP1,&SP2),RLSE),STORCLAS=&STORCLAS                   
//SORTWK2 DD DSN=&PRE..&SUBJECT..&UID..WK2,DISP=MOD,                           
//           SPACE=(TRK,(&SP1,&SP2),RLSE),STORCLAS=&STORCLAS                   
//SORTWK3 DD DSN=&PRE..&SUBJECT..&UID..WK3,DISP=MOD,                           
//           SPACE=(TRK,(&SP1,&SP2),RLSE),STORCLAS=&STORCLAS                   
//SORTWK4 DD DSN=&PRE..&SUBJECT..&UID..WK4,DISP=MOD,                           
//           SPACE=(TRK,(&SP1,&SP2),RLSE),STORCLAS=&STORCLAS                   
//SORTOUT DD DSN=&PRE..&SUBJECT..&UID..OUT,DISP=MOD,                           
//           SPACE=(TRK,(&SP1,&SP2),RLSE),STORCLAS=&STORCLAS                   
//SYSMAP  DD DSN=&PRE..&SUBJECT..&UID..MAP,DISP=MOD,                           
//           SPACE=(TRK,(&SP1,&SP2),RLSE),STORCLAS=&STORCLAS                   
//SYSERR  DD DSN=&PRE..&SUBJECT..&UID..ERR,DISP=MOD,                           
//           SPACE=(TRK,(&SP1,&SP2),RLSE),STORCLAS=&STORCLAS                   
//SYSDISC DD DSN=&PRE..&SUBJECT..&UID..DISC,DISP=MOD,                           
//           SPACE=(TRK,(&SP1,&SP2),RLSE),STORCLAS=&STORCLAS                   
//SYSREC00  DD  DISP=SHR,DSN=&INFILE                                           
//UTPRINT DD SYSOUT=&OUT                                                       
//SYSPRINT DD SYSOUT=&OUT                                                       
//SYSIN    DD DSN=SYS3.DB.TS0R0.PARMLIB(DB2LOAD1),DISP=SHR                     
//         DD DSN=SYS3.DB.TS0R0.PARMLIB(DB2TYPE&TYPE),DISP=SHR                 
//         DD DSN=SYS3.DB.TS0R0.PARMLIB(DB2COPY&TYPE),DISP=SHR                 
//         DD DSN=&&INCARD1,DISP=(SHR,DELETE,DELETE)                           
//         DD DSN=&PREPOS..PARMLIB(&FLDPOS),DISP=SHR                           
//IFEND    ENDIF                                                               

Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Tue Mar 15, 2011 8:32 pm
Reply with quote

Thank you icon_smile.gif

d
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 Unload and Load ISPF Table TSO/ISPF 4
No new posts ISPF Table to add a new column TSO/ISPF 1
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 How to load to DB2 with column level ... DB2 6
Search our Forums:

Back to Top