Home      Mainframe Forum      Mainfarme Tutorials      IBM Manuals      Mainframe Interview Questions      Mainframe Books      IT News     SiteMap     Downloads


     
 
MAINFRAME - TIP OF THE DAY : programming pearls - Good judgement comes from experience, and experience comes from bad judgement.

Google
 
Web mainframegurukul.com

Programmers Voted for below topics.      Please Vote for good Posts. Votes Salutes
Topic Title Votes Salutes
EJECT Verb in COBOL 21
Cobol Multiple Choice Questions By Shravan Wipro 16
what is index or subscript in cobol. please give me brief ex 14
what is static or dynamic call in cobol 13
jcl sort to add leading zeroes 13
SOC 7 abend - interview question 13
How to resolve soc7? 12
Hexadecimal to decimal conversion - SORT JCL INREC OUTREC 12
Infosys Training Program for freshers 10
HOW TO FIND LINKAGE LENGTH 10
VSAM File status 23 ,but records are present in the file 7
splitting of records using cobol 6
JOIN UNPAIRED,F1,F2,ONLY 6
Endevor 6
Changes in copybook 6
Frequently used commands in FileAid. 5
Difference Between XCTL and LINK 5
Comparing two files 4
JCL return codes 4
COBOL Program performance tuning tips 4
Interview questions - 3 years Exp - BOA, HCL and CTS 4
Various Locking mechanism for cursors and other SQL? 4
GDG interview question 4
alphanumeric to numeric value padding with ZEROS in COBOL 3 3
How to Display COMP-3 value in report 3 3
JOIN UNPAIRED,F1,F2,ONLY 3 3
Sending an Email from JCL with attachment 3
Control flow from one STEP to another 3
Recently faced mainframe cobol Interview Questions. 3
RESTART - Executing 2 and 3 rd step in 5 step jcl 3
sort two files with same sort condition 3
static and dynamic calls 3
What are the issues related with correlated subqueries? 3
restart question 3
Eliminate duplicate records in file 3
What is the difference between Copy and Include? 3
JCL SORT - Count number of records in a file 3
Regarding TSQ 3
JCL to Purge All Held output? 3
IKJ56500I COMMAND DSN NOT FOUND 3
S322 abend while sorting records 3
Call & Execute a COBOL-DB2 from a COBOL program? 3
compare two files using SORT 3
Submit a JCL thru Cobol - sysout=(INTRDR,*) 3
Comparing 2 files based on Acct number without using tables 3
Sort fields difference 3
How to copy only one record after sorting to SORTOUT. 2 2
What is the difference between Copy and Include? 2 2
Difference between sysprint, sysout 2 2
Various Locking mechanism for cursors and other SQL? 2 2
When you specify mutiple datasets in a JOBLIB or STEPLIB 2 2
Merging two files into one based on certain key fields: 2 2
what is index or subscript in cobol. please give me brief ex 2 2
static and dynamic calls 2 2
DB2 Certification Guidelines 2 2
Cobol Multiple Choice Questions By Shravan Wipro 2 2
Hexadecimal to decimal conversion - SORT JCL INREC OUTREC 2 2
JCL Errors 2 2
What is the difference between SYNONYM and ALIAS? 2 2
sort two files with same sort condition 1 1
NUMERIC condition checking on Alphanumeric value with spaces 1 1
Fetching RDW of a VB file in Easytrieve 1 1
EZtrv+ - Is there a way to suppress ez-code from printing... 1 1
cics 1 1
JCL SORT - Count number of records in a file 1 1
Comparing two files 1 1
how to copy multiple vsam files into a single flat file 1 1
Difference between an Object Module and Load Module 1 1
Difference Between XCTL and LINK 1 1
CA7 scheduling 1 1
Regarding EXEC CICS ENQ / DEQ 1 1
Use of CHNG and PURGE ? 1 1
Ways to merge the parts of different rows in the single row. 1 1
Copy GDG to GDG (one to one). 1 1
File STatus 41 1 1
CICS ABEND CODES 1 1
Display WITH NO ADVANCING 1 1
Interview questions - 3 years Exp - BOA, HCL and CTS 1 1
What are the valid DSORG values ? 1 1
How to write - 'Hello world' program through JCL 1 1
EJECT Verb in COBOL 1 1
pls give me answers these are may help for jobhunters.. 1 1
Merging Datasets with removing duplicates 1 1
How to fetch and compare the system date in JCL 1 1
what is the diff btw jcl error, maxcc=12 or 8 and abend? 1 1
OVERRIDING A DD CARD IN JCL FOR A MULTIPLE PROC JOB 1 1
REXX Tools 1 1
JCL interview question answer Ebook 1 1
Infosys Training Program for freshers 1 1
Someone please explain this COMPUTE statement 1 1
Help in Multiplcation of decimal constant 1 1
JCL to run the DB2 Query - IKJEFT01 1 1
ICETOOL & DFSORT 1 1
Symbolic Parameters 1 1
JCL to interact with Oracle DB 1 1
SOC 7 abend - interview question 1 1
PROCLIB 1 1
Why browse is substituted on big datasets? 1 1
Create a loop in a JCL 1 1
what is static or dynamic call in cobol 1 1
Frequently used commands in FileAid. 1 1
How to resolve soc7? 22
JOIN UNPAIRED,F1,F2,ONLY 22
what is index or subscript in cobol. please give me brief ex 21
Hexadecimal to decimal conversion - SORT JCL INREC OUTREC 19
Frequently used commands in FileAid. 18
what is static or dynamic call in cobol 14
jcl sort to add leading zeroes 13
Cobol Multiple Choice Questions By Shravan Wipro 12
RESTART - Executing 2 and 3 rd step in 5 step jcl 12
JCL SORT - Count number of records in a file 11
SORT JCL - SORTING A VB FILE - INREC OUTREC PARSE BUILD 11
Joinkeys using ICETOOL 10
what is sync point in cics 8
Display WITH NO ADVANCING 8
How to concatenate records from two ps into one ps? 8
Interview questions - 3 years Exp - BOA, HCL and CTS 8
Sending an Email from JCL with attachment 7
EJECT Verb in COBOL 7
SOC 7 abend - interview question 7
Various Locking mechanism for cursors and other SQL? 7
How do you make your BMS maps case sensitive? 7
How to retrieve deleted dataset / pds in mainframe 6
What is the difference between Comp-5 and Comp in Cobol ?? 6
Control flow from one STEP to another 6
what is the step debug the online program to use xpediter 6
Difference Between XCTL and LINK 6
what is the Length of DSN=.... 5
How to assign System Date/Time to a parameter in a JCL pgm? 5
Submit a JCL thru Cobol - sysout=(INTRDR,*) 5
How to compare two ps files having millions of records ? 5
abend code S000 U4038 5
Recently faced mainframe cobol Interview Questions. 5
Replacing a larger string with smaller string 5
VSAM File status 23 ,but records are present in the file 5
GDG interview question 5
How to run only 2 steps out of 100 steps??? 5
How we can access PDS and its Members thru COBOL ? 4
SORT INREC OUTREC OVERLAY - Adding sequential numbers 4
Mainframe Developer - Cognizant - Chennai 4
Sort fields difference 4
how to concatenate the datasets 4
static and dynamic calls 4
How to fetch and compare the system date in JCL 4
What is the function of //JCLLIB statement? 4
Comparing 2 files based on Acct number without using tables 4
FILE-AID vs InSync 4
How to compile list a coolgen batch code using endeavor 4
In cics what is new copy.please give me a example. 4
Difference between an Object Module and Load Module 4
How to write - 'Hello world' program through JCL 3
CA7 scheduling 3
DB2 query !! 3
Regarding TSQ 3
SQLCODE -811, why always 2nd row is fetched?? 3
Set Return Code using DFSORT 3
SYSTEM COMPLETION CODE=0C4 3
FOREIGN KEY 3
Problem with numeric field with sign in last digit 3
How to find record count of a mainframe dataset? 3
Reg DFSORT utility. 3
INFO about REGION parm 3
In SMS datasets, what is the function of the DD MGMTCLAS 3
To connect EBCDIC records to ASCII during write process 3
compare two files using SORT 3
How to read HEX file and convert it into decimal in REXX. 3
alphanumeric to numeric value padding with ZEROS in COBOL 3
How do you restart a proc from a particular step in jcl? 3
COMP-3 TO ALPHANUMERIC MOVE 3
Cobol question 3
How can a JES3 operator command be entered through the job 3
Merging Datasets with removing duplicates 3
NUMERIC to COMP-3 move 3
JCL to copy from DASD to TAPE 3
JCL-I need to FTP from MF to Local computer 3
difference between Omegamon and Mainview 3
What is MQ SERIES AND why it is used? 3
sort two files with same sort condition 3
PACKAGE VS PLAN ? 3
compile the cics,db2 or cobol program what next step- 3
What is the difference between Copy and Include? 3
sign stored in Packed Decimal fields and Zoned Decimal field 3
Endevor 3
Urgent Help needed !! 3
Editing a Copybook - How to? 3
Replace special characters with spaces in a string 2
what s the difference between DB2 & ADB2? 2
IEBGENER - Emptying dataset. 2
Reformatting and calculating time and date values 2
Infosys Training Program for freshers 2
SQL Query using REXX 2
Mainframe jobs in Germany 2
Need to compare two files using sort in jcl 2
Sort and merge two files with a condition 2
What is auditing? 2
DB2 Certification Guidelines 2
Define/Create VSAM-E file 2
ICETOOL & DFSORT 2
Computer based tutorial on IMS DB/DC 2
help me in creating EXCEL document as OUTPUT FROM COBOL PROG 2
How to know the Primary and Foriegn keys? 2
How can i attach my sig? 2
to merge 2 records of the input file using SORT 2
Immediate Mainframe opening in CSC,Chennai 2
What is the attribute byte? 2
How to write a JCL using IEFBR14 2
A Really Nice One. Don't Miss the Moral. 2
Redefine with occur clause 2
To get the total record count of flat file..... 2
JCL - INCLUDE Condition - SORT INCLUDE COND 2
Executing n th step in a proc - JCL - IEBEDIT 2
Create files with matching and non matching records- SPLICE? 2
block size? 2
pls give me the answers for the following interview quesions 2
how to extract real time in easytrieve 2
To split matched and unmatched records into 2 files 2
Create a loop in a JCL 2
To compare two files (SSN fields) 2
PROCLIB 2
Mainframe PS to Excel and mail it. 2
Cobol Test 1 - Interview Questions 2
benefits with SYNC 2
DATACLAS 2
stored procedure in cobol 2
REXX Tools 2
DB2 PERFORMANCE TUNING TIPS 2
Converting file of RECFM=U to RECFM=FB 2
What is SSRANGE, NOSSRANGE ? 2
Replacing characters Using JCL 2
DBXX NOT OPERATIONAL, RETRY COUNT IS ZERO 2
Comparing two files 2
Eliminate duplicate records in file 2
How to Display COMP-3 value in report 2
COBOL TEST-3 WITH ANSWERS 2
What is a DBRM, PLAN ? 2
VERTICAL ARRAY IN CICS 2
CICS ABEND CODES 2
SOC1 abend 2
generating sequence number starting from 0100000000000000 2
Production Support 2
how to calculate age 2
What is Static,Dynamic linking ? 2
How to send mail having some content of the file via jcl 2
CA-7 1
Need to check count of file records and abend if >10. 1
Moving cursor to next field in ISPF dialog 1
JCL to run the DB2 Query - IKJEFT01 1
CODE THAT WAIT X MINUTES BEFORE EXECUTE A STEP 1
VSAM File status 34 1
Merged file to remove duplicate so order is not affected 1
Comapare Two Records in the same File 1
Merging of two files with non-unique keys. 1
how to merge two flat files in append manner 1
how to deal with the below scenario using iebgener 1
user abend in easytrieve 1
using Index other than subscript 1
Compare to files - write the count of matching - Easytrieve 1
Can we take XDC of the production job run? 1
too many tables join 1
Hai to All 1
SORT assistance for setting up RC 1
EVALUATE Statement in Easytrieve 1
What is the difference between SYNONYM and ALIAS? 1
How to convert a decimal valuie to hexadecimal using sort 1
How can you override/nullify Condition code (COND) in jcl? 1
JCL Interpreter :?: 1
conversion by using SORT 1
EASYTRIEVE 1
Name the macros to be used to define MAP, MAPSET, FIELD 1
need to check a GDG generation is present in catalog or not 1
Technical diff between Microfocus cobol and Mainframe Cobol 1
How to write sort card? 1
Masking some fields in a dataset using JCL 1
Comparing Date with current date 1
SORT verb - help 1
Find and Replace using DFSORT 1
maxcc=1 1
how to insert new column in already existing vsam file 1
How to compile codes in changeman through tool in mainframe. 1
Can any one let me know how to resolve +100,-803,-811 1
Generate list of batch jobs in jobtrac using jcl 1
Mainframe Refresher eBook 1
Disabling CICS 1
How to use both lower and upper case letters in cobol? 1
IBM Mainframe certification courses and exam 1
How to FTP a dataset from my mainframe to personal Folder 1
is the answer correct?? 1
Comp-3 variables to Comp variables. 1
retrieving deleted GDG version 1
Reset Condition Code. 1
Production Support 1
executing java program thru JCL 1
Problems associated with each type of interview question 1
HOW TO FIND LINKAGE LENGTH 1
what is the diff btw jcl error, maxcc=12 or 8 and abend? 1
a Sub-Query and a Correlated Sub-Query 1
How can i delete records form a VSAM file. 1
BMC Load REPLACE option 1
Endevor 1
Intertest tool help 1
Mainframe Developer Requirement - Coimbatore 1
PACKMODE ON/OFF 1
What is use of redefine with real time example? 1
Compile error (Cobol using copy book) 1
Stop Run in both called and calling programs !!! 1
COBOL Program performance tuning tips 1
resource unavailable..table is not accessible 1
SQL Code -913 1
88 Level default value 1
Man of the year 1
SYSUDUMP and SYSABEND 1
compare 2 files with different LRECL 1
User abend 4038 1
JCL return codes 1
Using Pointers in a CICS-COBOL program 1
How to copy only one record after sorting to SORTOUT. 1
I need to FTP from MF to UNIX 1
How to handle the null indiactor 1
what is INTRDR ( Internal Read) in JCL ? 1
is multiple STOP RUN are valid? 1
please clear my doubt 1
JCL Error S878 1
Simple JCL Tutorial & IBM Tutorials Manuals Pdf Tutor 1
Need sort to compare 2 datasets 1
What is the difference between CANCEL and INITIAL statement 1
Help regarding Career in Vision Plus, Mainframe 1
S322 abend while sorting records 1
Reg: DDIO file in XPED 1
EZT+ Date manipulation - How do I subtract 30 days sysdate 1
How to convert Julian date format into yyyymmdd date format? 1
Can any one let me know how to resolve +100,-805,-803. 1
QSAM 1
File STatus 41 1
what is STOP RUN OR GOBACK OR EXIT PROGRAM 1
Restart logic (Checkpoint logic) 1
DB2 COMMIT?? 1
Endevor tutorial 1
Requirement in INAUTIX - CHENNAI Exp - 3 to 6 Years 1
Difference between physical and symbolic map 1
PL/I program - PROC OPTION various attributes 1
Converting Easytrev to EZTPA00 1
I am beginer in db2 , why r using bind ,plan & packge? 1
COBOL MOVE CORRESPONDING DOUBT 1
What is the difference between Static and Dynamic Binding? 1
Delete a column from a file 1
File-Aid Batch accessing copybook 1
DFSORT - JOINKEYS 1
How to check the return code of PROC 1
Copy of Fixed Length file into Variable Length File 1
UDS2000 utility program 1
how to download table data with out extra ? for null values 1
create a table in such a way that Delete cannot be used? 1
Call & Execute a COBOL-DB2 from a COBOL program? 1
Omitt records from sort but still include them in output 1
To match and remove duplicate records from two files. 1
make a output file from two input files on certain condition 1
In jcl please help me. 1
Appending data to a PDS using REXX 1
CA-7 standby 1
HOW TO INCREASE DATASET SPACE 1
XREF Creation 1
JCL to Purge All Held output? 1
To delete a segment in an IMS DB & create XREF using INS 1
What are the contents of a DCLGEN? 1
Multiple Conditions to Copy a File 1
why include can't be used in procedure division? 1
What is DCLGEN ? 1
EDIT a STRING 1
searching for a special character through sort 1
Join on keys and replace when matched 1
utility IKJEFT1B 1
LEFT OUTER JOIN IN COBOL VSE ENVIRONMENT 1
Access DB2 region using rexx 1
Regarding Restart card in JCL 1
Is it possible to add timestamp in the Header Record? 1
Inserting a Delimiter 1
hai 1
delete a record in ps file. 1
To get Volume Serial Number for Uncatalog Dataset 1
Getting Error while using ICEMAN tool 1
DFSORT Sorting a pack decimal field. 1
How do you retrieve the data from a nullable column? 1
DFHAC2016 - Trans XXXX cannot run because program XXX is Not 1
Array setup 1
Copying GDG 1
PARM PARAMETER 1
SORTCARD help 1
How to delete PDS member alone thru a jcl 1
how to split files which has header and trailor 1
link-edit error 1
EZtrv+ - Is there a way to suppress ez-code from printing... 1
Capgemini Mainframe opportunity 1
Difference between internal sort and external sort 1
How to use XREF.. 1
Icetool- Splicing : More than one compare key 1
Add Header and Trailer record also display the recod Count 1
LINKING of Sub-Program 1
DB2 checkpoint-restart doubts 1
sort the input file excluding the trailer record 1
Ezyedit selsection panel 1
HOW TO CAPTURE JOBLOG THROUGH JCL 1
search the given PS file for a string 1
Dynamic name allocation to a file 1
REXX tool needed to capture the JCL syntax errors 1
How do you code an instream data in a proc 1
Sending the user a message when job abends? 1
NUMERIC condition checking on Alphanumeric value with spaces 1
Vaccination for Children 1
joins examples on multiple tables 1
Rexx/Clist to auto-build IEFBR14 for JCL 1
cics 1
"Merge" two files in a special way 1
file manager(unable to view/edit dataset) 1
SORT JCL to Split data using OUTFIL 1
Easytrieve Job Input statement query 1
SE37 space abend 1
Compare 2 files and write O/P accordingly 1
equivalent file-manager command of file-aid command COPYBACK 1
Override 1
Connect Direct to copy data from mainframe to desktop. 1
How to update records count in the header using JCL 1
difference between -805 and -818 sqlcode 1
PACKED DECIMAL TO NUMERIC USING EASYTRIEVE 1
Pointers in Cobol 1
U4093 IMS Abend 1
writing 2 records for each qualifying input record. 1
Inserting NULL 1
COUNT of records based on distinct field value(ICETOOL/SORT) 1
Is it possible to get last modified date of PDS members... 1
Two files Matching Logic 1
Mainframe Jobs - Pune 1
Merging two files into one based on certain key fields: 1
how to describe a table in spufi 1
Difference between Ps and ESDS 1
Use of CHNG and PURGE ? 1
How to copy specific members via fileaid 1
Ways to merge the parts of different rows in the single row. 1
types of cursors? 1
Sorting - SORT INREC OUTREC SUM FIELDS 1
SYNCSORT trick 1
How to write a JCL using IEBCOMPR 1
SORT - Ifthen, when group question 1
S0C6 Abend while Opening a DB2 Cursor in a DB2-PLI Proram 1
How to Separate Odd or Even numbers using DFSORT 1
CICS Tutorial 1
Help required for a sort step. 1
Corresponding statement 1
What is length in sysin dd? 1
What is the difference between CS and RR isolation levels? 1
Tool to practice COBOL Programs 1
Question about Files matching logic 1
Using IDCAMs how to check whether file empty or not 1
Fetching Previous Day's date in my JCL

 
Post new topic   Reply to topic    mainframegurukul.com Forum Index -> JCL
  View previous topic :: View next topic  
Author Message
bhaskar.naidu
Member


Joined: 06 Jul 2009
Posts: 16

Answer was approved by Question owner.
Useful Post
0 votes
Not an useful post
Greate Post!! Salute Author
0 salutes

PostPosted: Mon Jul 06, 2009 5:30 pm    Post subject: Fetching Previous Day's date in my JCL Reply with quote

Hi,
In my JCL, I am using DNSTIAUL to fetch records from a table which has a huge number of records in which My where clause is trying to get the previous day's date using the timestamp from the table. This Query is taking a lot of time in executing the JCL.
Is there any way by which I can reduce the execution time?

Regards,
Bhaskar
Back to top
View user's profile Send private message
Click here, If this post answer your question. <-- Click on right mark icon. If this post answer your question.

dbzTHEdinosauer
Moderator


Joined: 02 Oct 2006
Posts: 975

Answer was approved by Question owner.
Useful Post
0 votes
Not an useful post
Greate Post!! Salute Author
0 salutes

PostPosted: Mon Jul 06, 2009 5:47 pm    Post subject: Reply with quote

what's today?, physic day. what does your WHERE clause look like?
_________________
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.
Back to top
View user's profile Send private message
Click here, If this post answer your question. <-- Click on right mark icon. If this post answer your question.
dbzTHEdinosauer
Moderator


Joined: 02 Oct 2006
Posts: 975

Answer was approved by Question owner.
Useful Post
0 votes
Not an useful post
Greate Post!! Salute Author
0 salutes

PostPosted: Mon Jul 06, 2009 5:57 pm    Post subject: Reply with quote

and:
how many rows are in the table
how many rows do you expect to select
is the timestamp part of an index
how large are the rows - how many columns
what do you think is a 'long time'?
?????
_________________
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.
Back to top
View user's profile Send private message
Click here, If this post answer your question. <-- Click on right mark icon. If this post answer your question.
bhaskar.naidu
Member


Joined: 06 Jul 2009
Posts: 16

Answer was approved by Question owner.
Useful Post
0 votes
Not an useful post
Greate Post!! Salute Author
0 salutes

PostPosted: Mon Jul 06, 2009 6:06 pm    Post subject: Reply with quote

Answers to your queries:
how many rows are in the table - there are 15 million records in the table
how many rows do you expect to select - I am expecting around 2K rows from the select
is the timestamp part of an index - we are also thinking of this as a solution, but would appreciate if I get any different approach.

how large are the rows - how many columns - Columns are not more, there are around 10 columns, but there are 15 million rows
what do you think is a 'long time'? - it is taking around 45 mins to 65 mins.
?????[/quote]

My where clause is as given below:

WHERE DATE(AUDIT_UPDT_TS) = DATE(CURRENT DATE - 1 DAY)
Back to top
View user's profile Send private message
Click here, If this post answer your question. <-- Click on right mark icon. If this post answer your question.
dbzTHEdinosauer
Moderator


Joined: 02 Oct 2006
Posts: 975

Answer was approved by Question owner.
Useful Post
0 votes
Not an useful post
Greate Post!! Salute Author
0 salutes

PostPosted: Mon Jul 06, 2009 6:21 pm    Post subject: Reply with quote

well your WHERE clause performs a scalar function on each and every row.
so I would change the clause to be
WHERE AUDIT_UPDT_TS BETWEEN (figure out the functions necessary to make CURRENT_DATE - 1 day concatenated with 00:00:00.000000)
AND (figure out the functions necessary to make CURRENT_DATE - 1 day concatenated with 23:59:59.999999)

db2 will generate the right side of the predicate once.

if this unload is important for the business, create a new index, obviously not unique.

but the change in the WHERE clause should help.
_________________
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.
Back to top
View user's profile Send private message
Click here, If this post answer your question. <-- Click on right mark icon. If this post answer your question.
bhaskar.naidu
Member


Joined: 06 Jul 2009
Posts: 16

Answer was approved by Question owner.
Useful Post
0 votes
Not an useful post
Greate Post!! Salute Author
0 salutes

PostPosted: Tue Jul 07, 2009 10:38 am    Post subject: Reply with quote

Hi Dick, your query worked and is perfect for my requirement. Thanks a lot for your timely help.
Back to top
View user's profile Send private message
Click here, If this post answer your question. <-- Click on right mark icon. If this post answer your question.
dbzTHEdinosauer
Moderator


Joined: 02 Oct 2006
Posts: 975

Answer was approved by Question owner.
Useful Post
0 votes
Not an useful post
Greate Post!! Salute Author
0 salutes

PostPosted: Tue Jul 07, 2009 1:20 pm    Post subject: Reply with quote

glad it helped. be interesting to know the difference in CPU time (not elapsed, please).

the difference is it the amount of time db2 spent manipulating the timestamp datatype to date datatype 15 million times.

and please, it is ROWS not records for db2.
_________________
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.
Back to top
View user's profile Send private message
Click here, If this post answer your question. <-- Click on right mark icon. If this post answer your question.
bhaskar.naidu
Member


Joined: 06 Jul 2009
Posts: 16

Answer was approved by Question owner.
Useful Post
0 votes
Not an useful post
Greate Post!! Salute Author
0 salutes

PostPosted: Tue Jul 07, 2009 1:28 pm    Post subject: Reply with quote

Hi Dick,

I really dont know how to check for the amount of time db2 spent manipulating the timestamp datatype to date datatype. If you can tell me how to check that, I shall try it and let you know..

But the original query was taking a time of 18.89 secs for 1500 rows where as this revised query took just 7.26 secs.

Regards,
Bhaskar
Back to top
View user's profile Send private message
Click here, If this post answer your question. <-- Click on right mark icon. If this post answer your question.
dbzTHEdinosauer
Moderator


Joined: 02 Oct 2006
Posts: 975

Answer was approved by Question owner.
Useful Post
0 votes
Not an useful post
Greate Post!! Salute Author
0 salutes

PostPosted: Tue Jul 07, 2009 1:50 pm    Post subject: Reply with quote

the 18.89 secs and 7.26 secs are what I was looking for. you mentioned 45 to 65 minutes, which I assumed was elapsed. Don't need to respond, but what is the new elapsed time.

and actually the time involved is for reading and qualifying rows from 15 million total.
Of course the number of selected will affect the time,
but the key here is 15 million rows had timestamp columns where each was converted in preparation for the WHERE condition.

As far as how much of the time db2 spent actually converting - not even going to guess.
we only know that a significant amount/portion/percentage of time was saved by not performing the scalar function on the column, 15 million times.
_________________
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.
Back to top
View user's profile Send private message
Click here, If this post answer your question. <-- Click on right mark icon. If this post answer your question.
bhaskar.naidu
Member


Joined: 06 Jul 2009
Posts: 16

Answer was approved by Question owner.
Useful Post
0 votes
Not an useful post
Greate Post!! Salute Author
0 salutes

PostPosted: Tue Jul 14, 2009 10:36 am    Post subject: Reply with quote

Hi Dick,

In continuation with this conversation, my JCL is exexcuting DNSTIAUL utility to fetch records from a table for which you gave a solution to use

"WHERE AUDIT_UPDT_TS BETWEEN (figure out the functions necessary to make CURRENT_DATE - 1 day concatenated with 00:00:00.000000)
AND (figure out the functions necessary to make CURRENT_DATE - 1 day concatenated with 23:59:59.999999)"

So, I changed the Where clause as given below:

WHERE AUDIT_UPDT_TS BETWEEN (CHAR(CURRENT DATE - 1) concatenated with 00:00:00.000000) AND (CHAR(CURRENT DATE - 1 day) concatenated with 23:59:59.999999).

But then my DBA advised me that it is still using the CHAR function in DSNTIAUL which is not allowed in our organization as per the new standards.

So, my idea is to have a SORT utility in the first step where it would calculate this WHERE clause and get the hard-coded audit-updt-ts values replaced and prepare the query and put it in a control card or a dataset for my second step which would execute the DSNTIAUL.
could you please let me know whether we can do this using SORT or not.
If yes, please help me.
Back to top
View user's profile Send private message
Click here, If this post answer your question. <-- Click on right mark icon. If this post answer your question.
arcvns
Member


Joined: 30 May 2009
Posts: 28
Location: Chennai, India

Answer was approved by Question owner.
Useful Post
0 votes
Not an useful post
Greate Post!! Salute Author
0 salutes

PostPosted: Thu Aug 06, 2009 3:40 pm    Post subject: Reply with quote

Hello,

May be it's too late, but you can generate the above WHERE condition using the below SyncSort job.
Code:
//STEP1  EXEC PGM=SORT                                       
//SYSOUT   DD SYSOUT=*                                       
//SORTIN   DD *                                               
//SORTOUT  DD SYSOUT=*                                       
//SYSIN    DD *                                               
  OPTION COPY                                                 
  OUTFIL REMOVECC,NODETAIL,BUILD=(80X),                       
         TRAILER1=('WHERE AUDIT_UPDT_TS BETWEEN',/,'(''',     
                   DATE=(4MD-)-1,'-00.00.00.000000'' AND ''',
                   DATE=(4MD-)-1,'-23.59.59.999999'')')
SORTOUT would have this,
Code:
WHERE AUDIT_UPDT_TS BETWEEN                                   
('2009-08-05-00.00.00.000000' AND '2009-08-05-23.59.59.999999')

_________________
Arun
Back to top
View user's profile Send private message Yahoo Messenger
Click here, If this post answer your question. <-- Click on right mark icon. If this post answer your question.
Natarajan
Moderator


Joined: 10 Oct 2008
Posts: 537
Location: chennai

Answer was approved by Question owner.
Useful Post
0 votes
Not an useful post
Greate Post!! Salute Author
0 salutes

PostPosted: Thu Aug 06, 2009 3:59 pm    Post subject: Reply with quote

Bhaskar has initiated separte thread for this... There is a answer from skolusu using DFSORT... it may be useful for those ,we is reading this thread.

http://www.mainframegurukul.com/MainframeGuru/viewtopic.php?t=4290
_________________
Natarajan
Chennai
Back to top
View user's profile Send private message Visit poster's website
Click here, If this post answer your question. <-- Click on right mark icon. If this post answer your question.
Display posts from previous:   
Post new topic   Reply to topic    mainframegurukul.com Forum Index -> JCL All times are GMT + 5 Hours
Page 1 of 1



 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Related topics
 Topics   Replies   Author   Views   Last Post 
No new posts What are all the COBOL compilers available? 6 Sweetu 14509 Mon Feb 01, 2016 10:48 pm
academyindia4 View latest post
No new posts Multiple jobs trying to write to same GDG simultaneously 1 anuthereaper 697 Sun Jun 22, 2014 11:43 pm
NicC View latest post
No new posts Trying to create a report using DFSORT but stuck on DATES 6 skotikila2 2083 Tue May 06, 2014 5:06 pm
skotikila2 View latest post
No new posts Trying to delete/define a VSAm, but VSAM is in use 10 Dr_Halo 9388 Sat Mar 21, 2009 8:14 am
Anuj Dhawan View latest post
No new posts Hi while trying to copy a flat file to KSDS VSAM file , an e 1 SUBARNA ROY 6654 Sat Jun 21, 2008 2:29 pm
MikeJ024 View latest post
 


Cobol Tutorial
This cobol tutorial covers most of the important topics like STRING, UNSTRING, COMP, COMP-3.....
DB2 Tutorial
DB2 Tutorial focuses on DB2 COBOL Programming. Explains in simple language. Some Chapters are locked, Forum members have free access to these chapters
CICS Tutorial
This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming...
JCL Tutorial
This is most popular JCL tutorial from mainframegurukul. It does contain important jcl ....
SORT Tutorial
This Tutorial covers all important aspects of DFSORT. Has more SORT examples
INTERVIEW Questions
This page covers important interview questions
Mainframe Jobs
Mainframe Jobs posted by members of mainframegurukul forum
FaceBook Page
MainframeGurukul FaceBook Page
LinkedIn Page
MainframeGurkul Linkedin Page

This widget requires Flash Player 9 or better







Go to top of the page
 

Online ABEND Reference ||  JCL References ||  COBOL References ||  VSAM References ||  Tutorials by Drona Series ||  SQL tutorial ||  BOOKS  ||  DB2 INTERVIEW QUESTIONS ||  COBOL INTERVIEW QUESTIONS  ||  JCL INTERVIEW QUESTIONS ||  JCL2 INTERVIEW QUESTIONS ||  VSAM INTERVIEW QUESTIONS ||  CICS INTERVIEW QUESTIONS  ||  Online tutorials ||  Online ABEND Reference ||  JCL References ||  COBOL References ||  VSAM References ||  Tutorials by Drona Series ||  SQL tutorial ||  BOOKS  ||  SiteMap  ||  Expeditor Tutorial  ||  FILE-AID Tutorial  ||  Changeman Tutorial  ||  COBOL   ||  DB2   ||  JCL  ||  CICS  ||  VSAM  ||  DB2 Interview Questions ( 110 )   || Simple JCL Tutorials  || JCL Tutorial from MainframeGurukul.com   || Simple JCL Tutorial - Chapter1 ;|| Mainframe Forum - Tutorials  || Mainframe Tutorials || Mainframe CICS tutorial|| Mainframe COBOL Tutorial

Drona Educational Forums - Mainframe Cobol DB2 CICS Board
Powered by phpBB

mainframetopic-4253.html