Re: Unload data
John Gilmore wrote: ... as it is the clots who cannot be bothered to learn to do so. LOL! I had to look up 'clot' as I only know about clot as in 'blood clot'. http://www.thefreedictionary.com/clot said it is (noun) 'Brit - a stupid person; fool' ;-D In my experience the use of a sort to post process DB2 outputs reflects incompetent SQL. Claims that it is 'required', 'more efficient', and the like turn out upon examination to be specious. Very true and time wasting [1] to try to explain it to those SQL issuers... Groete / Greetings Elardus Engelbrecht I hate that IEC070I or DFHST0103 caused by SQL in a COBOL program... I use 'Cancel' as bullets to automatically get rid of those 'clots'. -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Unload data
Ron Thomas wrote: We have a huge table with around 125 million records and we are unloading this file in a job based on the current date, there are some instances where this job gets delayed and runs past 12.00 AM and the records fetched is not correct. Could some one please let me know in the unload whether we can pass some parms as we have a cycle file which has the current date stored and by this way we need not worry whether the job is delayed or not. and We are using DB-2 unload utility as the table size is 125 million, the Unload data is fed to the downstream programs for further processing. You did not answered all of Lizette's questions. My one question is: What is 'unload'? Is it a dump, copy (re-formatted contents or not) , SQL query, etc? Also you asked about 'parms', but you did not said what parms you tried out or against what program you're trying to use parms. I have other questions: What version of DB2 do you have? Is this an once off problem or not? For how long is it that you have that huge table? With what are you 'fetching' the records and storing it somewhere else? Some possible suggestions: Do your work hourly or give your job higher priority or split up your table in smaller chunks. Best solution was given by Ed Gould! Groete / Greetings Elardus Engelbrecht -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Unload data
Then you need to post your entire control cards for UNLOAD. And provide us with what UNLOAD Utility. Is it IBM's DB2 Unload or CA's DB2 Tools or OTHER? It would be better to post the entire step that runs long and one that runs normally. Otherwise you will continue to get questions from the list. That includes the JCL, the control cards and the messages produced at run time. If you are having issues with DB2 Unload, then you should open an problem ticket with the vendor for assistance. Without very specific information it is very difficult for the list to comment on your issue. There is a lot of wisdom on this list as well as other mainframe lists (like DB2, IMS, CICS, MQ, TSO-REXX and so forth). However if you want a comment on your issue, the more specific information you provide the less questions will be asked and perhaps some guidance on your issue could be provided. A List is not a help desk. Lizette -Original Message- From: IBM Mainframe Discussion List [mailto:IBM-MAIN@LISTSERV.UA.EDU] On Behalf Of Ron Thomas Sent: Monday, August 04, 2014 10:29 PM To: IBM-MAIN@LISTSERV.UA.EDU Subject: Re: Unload data We are using DB-2 unload utility as the table size is 125 million, the Unload data is fed to the downstream programs for further processing. Thanks Ron T -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Unload data
Ok Thanks every one, as i told this is a DB2 SQL query . We have found a solution to read the unload table query from the PDS and will be replacing the where clause current date with the date from the cycle file. Thanks Ron T -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Unload data
And I have another suggestion. There is a mail reflector/listserv for DB2 (if that is the data store here) with many experts and those whop have done similar functions like what you are asking about. I suggest they you go over to idug.org (international db2 user group) and ask the question there. though the more information you provide the faster the response you will get. here or there On 2014-08-05, 2:18 AM, Elardus Engelbrecht wrote: Ron Thomas wrote: We have a huge table with around 125 million records and we are unloading this file in a job based on the current date, there are some instances where this job gets delayed and runs past 12.00 AM and the records fetched is not correct. Could some one please let me know in the unload whether we can pass some parms as we have a cycle file which has the current date stored and by this way we need not worry whether the job is delayed or not. and We are using DB-2 unload utility as the table size is 125 million, the Unload data is fed to the downstream programs for further processing. You did not answered all of Lizette's questions. My one question is: What is 'unload'? Is it a dump, copy (re-formatted contents or not) , SQL query, etc? Also you asked about 'parms', but you did not said what parms you tried out or against what program you're trying to use parms. I have other questions: What version of DB2 do you have? Is this an once off problem or not? For how long is it that you have that huge table? With what are you 'fetching' the records and storing it somewhere else? Some possible suggestions: Do your work hourly or give your job higher priority or split up your table in smaller chunks. Best solution was given by Ed Gould! Groete / Greetings Elardus Engelbrecht -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Unload data
On Tue, 5 Aug 2014 08:47:31 -0500, Ron Thomas wrote: Ok Thanks every one, as i told this is a DB2 SQL query . We have found a solution to read the unload table query from the PDS and will be replacing the where clause current date with the date from the cycle file. I'm slightly surprised that current-date is evaluated for each row inspected rather than once at the start of the query. Or does the problem arise because depending on workload it's unpredictable on which date the query is issued? Anyway, sounds like a proper solution. -- gil -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Unload data
Ok The current date is one of the fields in the where clause. There are few others too. Let me know if there is any other way to handle ?. Thanks. -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Unload data
On Tue, Aug 5, 2014 at 6:03 PM, Ron Thomas ron5...@gmail.com wrote: Ok The current date is one of the fields in the where clause. There are few others too. Let me know if there is any other way to handle ?. Thanks. This really should be on the DB2-L list. But that's not important. (Airplane!) You didn't show the WHERE clause. So I must try to guess. I'll guess that the WHERE uses the CURRENT_DATE special register. E.g. WHERE some-var = CURRENT_DATE . The somv-var is a DATE field in each row of the table. You might get away with a WHERE clause like: WHERE some-var = CAST(CURRENT_TIMESTAMP - 5 HOURS AS DATE) . Hopefully the previous expression is obvious in intent. But it basically sets the current DATE to start at 05:00:00 and end at 04:59:59.99 on the next day. So, as long as the job runs on or after 05:00:00 of day n and strictly before 05:00:00 of day n+1, then the WHERE will be TRUE for day n. Wish I were a better communicator. It may be nasty to say, but I find it puzzling that this is not blatantly obvious to whomever is responsible for crafting your SQL queries. Unless, of course, there is more to this than has been revealed as yet. Again: POST THE SQL! It will make things much easier. Redact the names if they are somehow proprietary or not to be revealed under pain of pain. -- There is nothing more pleasant than traveling and meeting new people! Genghis Khan Maranatha! John McKown -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Unload data
Bracket the potential dates in the SELECT and eliminate the ones you don't want using DFSORT in a later step. So if you are using CURRENT_DATE in the SELECT use = CURRENT_DATE for the situation where the job clicks over to next date. 125 million rows is not a lot for DFSORT to filter later. On Wed, Aug 6, 2014 at 9:56 AM, John McKown john.archie.mck...@gmail.com wrote: On Tue, Aug 5, 2014 at 6:03 PM, Ron Thomas ron5...@gmail.com wrote: Ok The current date is one of the fields in the where clause. There are few others too. Let me know if there is any other way to handle ?. Thanks. This really should be on the DB2-L list. But that's not important. (Airplane!) You didn't show the WHERE clause. So I must try to guess. I'll guess that the WHERE uses the CURRENT_DATE special register. E.g. WHERE some-var = CURRENT_DATE . The somv-var is a DATE field in each row of the table. You might get away with a WHERE clause like: WHERE some-var = CAST(CURRENT_TIMESTAMP - 5 HOURS AS DATE) . Hopefully the previous expression is obvious in intent. But it basically sets the current DATE to start at 05:00:00 and end at 04:59:59.99 on the next day. So, as long as the job runs on or after 05:00:00 of day n and strictly before 05:00:00 of day n+1, then the WHERE will be TRUE for day n. Wish I were a better communicator. It may be nasty to say, but I find it puzzling that this is not blatantly obvious to whomever is responsible for crafting your SQL queries. Unless, of course, there is more to this than has been revealed as yet. Again: POST THE SQL! It will make things much easier. Redact the names if they are somehow proprietary or not to be revealed under pain of pain. -- There is nothing more pleasant than traveling and meeting new people! Genghis Khan Maranatha! John McKown -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN -- Wayne V. Bickerdike -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Unload data
On Wed, 6 Aug 2014 10:30:19 +1000, Wayne Bickerdike wrote: Bracket the potential dates in the SELECT and eliminate the ones you don't want using DFSORT in a later step. So if you are using CURRENT_DATE in the SELECT use = CURRENT_DATE for the situation where the job clicks over to next date. 125 million rows is not a lot for DFSORT to filter later. Say what That's the (ex ?) DBA in you talking Wayne. As if we don't have enough trouble in the world from DBAs selecting everything, sorting it, throwing it away then doing it all again - when they only need a (smal) subset of the data. Aggghhh - go read Radoslaws' Anothe(r) SORT question thread again. DB2 is a pig as far as overall system management is concerned - just like every other database system. Give me everything (page-fixed), the rest of the users can go slowly stew in hell. Shane ... -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Unload data
Hello. We have a huge table with around 125 million records and we are unloading this file in a job based on the current date, there are some instances where this job gets delayed and runs past 12.00 AM and the records fetched is not correct. Could some one please let me know in the unload whether we can pass some parms as we have a cycle file which has the current date stored and by this way we need not worry whether the job is delayed or not. Thanks Ron T -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Unload data
Call the programmer. Ed On Aug 4, 2014, at 11:31 PM, Ron Thomas wrote: Hello. We have a huge table with around 125 million records and we are unloading this file in a job based on the current date, there are some instances where this job gets delayed and runs past 12.00 AM and the records fetched is not correct. Could some one please let me know in the unload whether we can pass some parms as we have a cycle file which has the current date stored and by this way we need not worry whether the job is delayed or not. Thanks Ron T -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Unload data
What do you use for an UNLOAD? Is this a VSAM, DB2, SAP, etc.. type file? Do have a program that generates the UNLOAD process? You have not provided any information that allows the list to understand this issue. Not all shops use the same process for unload. If you cannot explain your problem more clearly, the list probably cannot help. Lizette -Original Message- From: IBM Mainframe Discussion List [mailto:IBM-MAIN@LISTSERV.UA.EDU] On Behalf Of Ron Thomas Sent: Monday, August 04, 2014 9:31 PM To: IBM-MAIN@LISTSERV.UA.EDU Subject: Unload data Hello. We have a huge table with around 125 million records and we are unloading this file in a job based on the current date, there are some instances where this job gets delayed and runs past 12.00 AM and the records fetched is not correct. Could some one please let me know in the unload whether we can pass some parms as we have a cycle file which has the current date stored and by this way we need not worry whether the job is delayed or not. Thanks Ron T -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Unload data
We are using DB-2 unload utility as the table size is 125 million, the Unload data is fed to the downstream programs for further processing. Thanks Ron T -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
