Re: Unload DB2 table in CSV format
Just this week, I had to correct a routine for a customer of mine that outputs a line of character data in CSV format. The routine had some errors and flaws. I would like to tell you what I did and discuss some of the properties of the CSV format. First: the routine gets as input a number (number of items) and an array of char pointers that point to (null terminated) char data which have to be written to the output file in CSV format. All data should be char data; the numeric values have been converted to char before the call to that routine. Another parameter to the routine is the separation char, which is semicolon in our case (could well be comma or tab, no problem at all). Second: for CSV, it is ok to surround every field with quotes. ExCel etc. works perfectly with this and omits the quotes while reading the CSV. This is very good, because this way you don't have problems if the separation char appears inside the value fields. So this is what I did. Another goodie: if the content of the field is numeric, ExCel does sometimes strange things; it tries to interpret the value and converts for example currency values to date fields etc., which is plain nonsense. You get around this if you enclose all your fields in quotes. Of course, if would not be necessary for char fields that are not numeric. But we do it for all fields, that makes the logic simpler. Third: the only thing you have to consider: if a qoute appears inside your value fields, you have to double it. Forth: to reduce the size of the CSV file, I omit trailing blanks in the char fields, that is: in the loop that puts the chars into the output buffer, the position of the last non-blank char is stored in a temp variable, and the closing quote is put just behind that position, when the end of the output string is reached (implicit right trim of the output string at almost no cost). This reduced the output files to ca. 2/3 of the original size, without any effect on the apperance of the result in ExCel. I guess this is Turing-complete, BTW. This is exactly the same logic I implemented in the CSV output (and input) routine of my DB2 and Oracle ETL tool that I mentioned in some prior posts. Feel free to contact me offline, if you want to know more about this. To the List admins: I decided not to put Ad: in the subject, because this post in my opinion was a technical post for the most part. It is sometimes dfficult to separate pure advertising from technical information, how something is or should be done. I hope you can accept this. Kind regards Bernd Am 26.02.2015 um 18:51 schrieb Paul Gilmartin: On Thu, 26 Feb 2015 08:20:49 -0800, Sri h Kolusu wrote: Tony suggested the use of Tab (X'05') as delimiter which will avoid the problem of data already have the common delimiter comma. I stand by my assertion that lacking a priori knowledge that a character can not occur in the data the task becomes more difficult; not impossible. And I can't resist mentioning (again): http://xkcd.com/327/ This is the second time in this week that you had a comment about DFSORT. I am a developer of DFSORT and there are many cool things that DFSORT can do and I am more than willing to show them. However I also believe using the right utility/program for the right job. I do not suggest to use DFSORT for every solution I posted here. My apologies if I offended. I recognize that DFSORT isn't the only tool in your kit, and I'm aware that you suggest other techniques when appropriate. Still, since DFSORT appears to be the Swiss Army Knife of z/OS, I can't resist wondering, idly, whether DFSORT is Turing-complete. -- gil -- 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 DB2 table in CSV format
P.S. I looked up "Turing-complete." Now I have a headache. On 2/26/2015 11:51 AM, Paul Gilmartin wrote: On Thu, 26 Feb 2015 08:20:49 -0800, Sri h Kolusu wrote: Tony suggested the use of Tab (X'05') as delimiter which will avoid the problem of data already have the common delimiter comma. I stand by my assertion that lacking a priori knowledge that a character can not occur in the data the task becomes more difficult; not impossible. And I can't resist mentioning (again): http://xkcd.com/327/ This is the second time in this week that you had a comment about DFSORT. I am a developer of DFSORT and there are many cool things that DFSORT can do and I am more than willing to show them. However I also believe using the right utility/program for the right job. I do not suggest to use DFSORT for every solution I posted here. My apologies if I offended. I recognize that DFSORT isn't the only tool in your kit, and I'm aware that you suggest other techniques when appropriate. Still, since DFSORT appears to be the Swiss Army Knife of z/OS, I can't resist wondering, idly, whether DFSORT is Turing-complete. -- gil -- 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 DB2 table in CSV format
Paul, my original inspiration for suggesting the "Swiss Army Knife" came from a project some years ago where I was tasked with cleaning up a DB2 table where support from the database team was not possible for political reasons. Being hardly the expert (I never learned DB1!) I posted the question, to which the esteemed Frank Yaeger replied, of "is is possible for DFSORT to read a DB2 table as SORTIN?" He suggested the unload utility (I've forgotten the actual commands) via IKJEFT01 to create a sequential disk file, whereupon the 2nd step would perform the cleanup via some rather basic DFSORT commands. Fast forward several years and I'm doing similar data manipulation, not for cleanup purposes, but to accomodate the requirements of a non-z/OS software. I've become fond of x'05' having plagiarized the idea from a MS-Access proficient colleage who was doing the reverse, writing tabbed output so z/OS could parse it into fixed columns. I have encountered other characters that trigger MS-excel's column definition, the " for one, no doubt there are others. I simply convert them to x'40' via INREC (thanks Sri!). Other offending characters could be treated likewise. No doubt others of us have encountered a dataset where some characters we're not expecting would be troublesome, and depending on the desired final product, various techniques are available to create output acceptable to its destination. I differ with Sri only slightly, in that I look for uses for DFSORT for every imaginable scenario, both for its versatililty and its ease of coding. Having said that, the several suggestions I have received from him have proven to be work and time savers. I'm still digesting one particularly tricky one, for which I refuse to ask for guidance till I have exhausted my own attempts at understanding. Flashing backwards to the late 90s and the Y2K fix up madness, I was working for "company to remain nameless" that hired a large team of contractors as many other companies did. Having reviewed a large number of their code (COBOL & PL1) it was a surprise to find how many of those could have been (and were) replaced by simple DFSORT steps. People were writing programs to drop duplicate records, perform simple record selection and output reformatting, and get this, even sorting the records! That experience made me a DFSORT bigot. Being in the December of my career I'm still learning things in the APG that, despite having read it cover to cover many times, are new discoveries. So if I'm guilty of using a screwdriver to pry open a paint can, so be it. On 2/26/2015 11:51 AM, Paul Gilmartin wrote: On Thu, 26 Feb 2015 08:20:49 -0800, Sri h Kolusu wrote: Tony suggested the use of Tab (X'05') as delimiter which will avoid the problem of data already have the common delimiter comma. I stand by my assertion that lacking a priori knowledge that a character can not occur in the data the task becomes more difficult; not impossible. And I can't resist mentioning (again): http://xkcd.com/327/ This is the second time in this week that you had a comment about DFSORT. I am a developer of DFSORT and there are many cool things that DFSORT can do and I am more than willing to show them. However I also believe using the right utility/program for the right job. I do not suggest to use DFSORT for every solution I posted here. My apologies if I offended. I recognize that DFSORT isn't the only tool in your kit, and I'm aware that you suggest other techniques when appropriate. Still, since DFSORT appears to be the Swiss Army Knife of z/OS, I can't resist wondering, idly, whether DFSORT is Turing-complete. -- gil -- 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 DB2 table in CSV format
On Thu, 26 Feb 2015 08:20:49 -0800, Sri h Kolusu wrote: > >Tony suggested the use of Tab (X'05') as delimiter which will avoid the >problem of data already have the common delimiter comma. > I stand by my assertion that lacking a priori knowledge that a character can not occur in the data the task becomes more difficult; not impossible. And I can't resist mentioning (again): http://xkcd.com/327/ >This is the second time in this week that you had a comment about DFSORT. >I am a developer of DFSORT and there are many cool things that DFSORT can >do and I am more than willing to show them. However I also believe using >the right utility/program for the right job. I do not suggest to use >DFSORT for every solution I posted here. > My apologies if I offended. I recognize that DFSORT isn't the only tool in your kit, and I'm aware that you suggest other techniques when appropriate. Still, since DFSORT appears to be the Swiss Army Knife of z/OS, I can't resist wondering, idly, whether DFSORT is Turing-complete. -- gil -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Unload DB2 table in CSV format
Gil, Tony suggested the use of Tab (X'05') as delimiter which will avoid the problem of data already have the common delimiter comma. This is the second time in this week that you had a comment about DFSORT. I am a developer of DFSORT and there are many cool things that DFSORT can do and I am more than willing to show them. However I also believe using the right utility/program for the right job. I do not suggest to use DFSORT for every solution I posted here. Thanks, Kolusu IBM Mainframe Discussion List wrote on 02/25/2015 07:11:04 PM: > From: Paul Gilmartin <000433f07816-dmarc-requ...@listserv.ua.edu> > To: IBM-MAIN@LISTSERV.UA.EDU > Date: 02/25/2015 07:11 PM > Subject: Re: Unload DB2 table in CSV format > Sent by: IBM Mainframe Discussion List > > On Wed, 25 Feb 2015 17:42:16 -0600, Tony's Outlook via Mozilla wrote: > > >I'd certainly prefer zOS/batch/DFSORT. Unload the table to FB disk, use > >DF/SORT to insert x'05'(tab) or comma(CSV) where desired. I do this > >quite often to create a flat file that will eventually go back to MS > >excel/access. Millions of records? No big deal. > > > And suppose your data contain commas in some column, in some rows > but not all? E.g. "Babonas, T." Well, the designers should have split > those fields into separate columns, but sometimes they don't. Choose > a different separator? If you don't know a priori which character meets > that requirement you must do a preliminary scan to find one. And if > you find none you must establish a convention for escaping metacharacters. > I suppose DFSORT can do all this. (Hmmm... Has anyone proven that > DFSORT is Turing-complete? Or that it isn't?) > > Faced routinely with such a problem given a .xlsx file, I open it with > LibreOffice and export as HTML, which guarantees that its delimiter > tokens don't appear among the output data. > > Bernd probably has an effective solution. LibreOffice doesn't batch > at all well. > > -- gil > > -- > 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 DB2 table in CSV format
With my tool, you can choose the delimiter by parameter (some like semicolon instead of comma, because in some European countries the comma is used instead of decimal point to separate the decimal fraction - tab is possible too, of course), and char fields are enclosed into quotes (or apostrophes), if necessary. ExCel etc. deal with all this, it's tested. Furthermore, you can choose if you want column headings or not. If you have column headings (derived from the SQL result), you can later use those headings to reference your columns, if you use the CSV to do updates on the DB again. If not, you have to specify the numeric position of the column ... in any case, you have to tell the tool the data type in this case, because it does no pre scan of the file, as ExCel does. But this is for the load situation only; for unload, it's as easy as unlddn csvhead := unload qualif.db2table; or unlddn csv := select * from qualif.db2table where ...; Kind regards Bernd Am 26.02.2015 um 04:11 schrieb Paul Gilmartin: On Wed, 25 Feb 2015 17:42:16 -0600, Tony's Outlook via Mozilla wrote: I'd certainly prefer zOS/batch/DFSORT. Unload the table to FB disk, use DF/SORT to insert x'05'(tab) or comma(CSV) where desired. I do this quite often to create a flat file that will eventually go back to MS excel/access. Millions of records? No big deal. And suppose your data contain commas in some column, in some rows but not all? E.g. "Babonas, T." Well, the designers should have split those fields into separate columns, but sometimes they don't. Choose a different separator? If you don't know a priori which character meets that requirement you must do a preliminary scan to find one. And if you find none you must establish a convention for escaping metacharacters. I suppose DFSORT can do all this. (Hmmm... Has anyone proven that DFSORT is Turing-complete? Or that it isn't?) Faced routinely with such a problem given a .xlsx file, I open it with LibreOffice and export as HTML, which guarantees that its delimiter tokens don't appear among the output data. Bernd probably has an effective solution. LibreOffice doesn't batch at all well. -- gil -- 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 DB2 table in CSV format
On Wed, 25 Feb 2015 17:42:16 -0600, Tony's Outlook via Mozilla wrote: >I'd certainly prefer zOS/batch/DFSORT. Unload the table to FB disk, use >DF/SORT to insert x'05'(tab) or comma(CSV) where desired. I do this >quite often to create a flat file that will eventually go back to MS >excel/access. Millions of records? No big deal. > And suppose your data contain commas in some column, in some rows but not all? E.g. "Babonas, T." Well, the designers should have split those fields into separate columns, but sometimes they don't. Choose a different separator? If you don't know a priori which character meets that requirement you must do a preliminary scan to find one. And if you find none you must establish a convention for escaping metacharacters. I suppose DFSORT can do all this. (Hmmm... Has anyone proven that DFSORT is Turing-complete? Or that it isn't?) Faced routinely with such a problem given a .xlsx file, I open it with LibreOffice and export as HTML, which guarantees that its delimiter tokens don't appear among the output data. Bernd probably has an effective solution. LibreOffice doesn't batch at all well. -- gil -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Unload DB2 table in CSV format
I posted it on the DB2 list some days ago, and if you don't mind, I post it here, too, because it's the same topic. I am selling a product running with DB2 and Oracle DBs, that generates CSV files from SQL results. It runs on almost every platform (mainframe, Unix, Windows). CSV is one of many output formats; others are flat files, XML, and other proprietary formats, which contain not only data, but meta data (like datatypes), too. It is possible to work the other way, too, that is, load DB2 data from CSV files (and the other formats, too, of course). You can trigger insert statements from the data in the CSV files, or updates, or deletes ... any kind of SQL statement that modifies the DB. (You specify the SQL, together with a file description, is necessary, that is: if the file does not already contain the needed meta data). In combination, it is very easy to do database migrations and database schema changes using this tool. I've done this many times for different customers. Please feel free to contact me offline, if you are interested. The mainframe variant of this product is z/OS batch, and it runs very fast. No need to inserts tabs or commas in a second step ... Thank you Bernd Oppolzer Am 26.02.2015 um 00:42 schrieb Tony's Outlook via Mozilla: I'd certainly prefer zOS/batch/DFSORT. Unload the table to FB disk, use DF/SORT to insert x'05'(tab) or comma(CSV) where desired. I do this quite often to create a flat file that will eventually go back to MS excel/access. Millions of records? No big deal. -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Unload DB2 table in CSV format
I'd certainly prefer zOS/batch/DFSORT. Unload the table to FB disk, use DF/SORT to insert x'05'(tab) or comma(CSV) where desired. I do this quite often to create a flat file that will eventually go back to MS excel/access. Millions of records? No big deal. On 2/25/2015 4:47 PM, Rob Schramm wrote: And it can be run in batch. Either off platform on z/OS. Rob Schramm Senior Systems Consultant On Wed, Feb 25, 2015 at 5:46 PM, Rob Schramm wrote: If it is something smaller (not millions of rows), connect via JDBC and use SQL Workbench/j. It has plenty of options to pull the data into a CSV. Rob Schramm Senior Systems Consultant On Tue, Feb 24, 2015 at 10:50 AM, Lizette Koehler wrote: Try adding more volumes. The DB2 List thinks you need to double what you are using with the JCL you posted. Lizette -Original Message- From: IBM Mainframe Discussion List [mailto:IBM-MAIN@LISTSERV.UA.EDU] On Behalf Of Ron Thomas Sent: Monday, February 23, 2015 7:44 AM To: IBM-MAIN@LISTSERV.UA.EDU Subject: Re: Unload DB2 table in CSV format Ok Thanks . I am getting the below message when i provided the DCB Parameters "FMNBA324 A data set, or volume full condition occurred while attempting to save this data set" 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 -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Unload DB2 table in CSV format
If it is something smaller (not millions of rows), connect via JDBC and use SQL Workbench/j. It has plenty of options to pull the data into a CSV. Rob Schramm Senior Systems Consultant On Tue, Feb 24, 2015 at 10:50 AM, Lizette Koehler wrote: > Try adding more volumes. The DB2 List thinks you need to double what you > are using with the JCL you posted. > > Lizette > > > > -Original Message- > > From: IBM Mainframe Discussion List [mailto:IBM-MAIN@LISTSERV.UA.EDU] > > On Behalf Of Ron Thomas > > Sent: Monday, February 23, 2015 7:44 AM > > To: IBM-MAIN@LISTSERV.UA.EDU > > Subject: Re: Unload DB2 table in CSV format > > > > Ok Thanks . I am getting the below message when i provided the DCB > > Parameters > > > > "FMNBA324 A data set, or volume full condition occurred while attempting > to > > save this data set" > > > > > > 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 DB2 table in CSV format
And it can be run in batch. Either off platform on z/OS. Rob Schramm Senior Systems Consultant On Wed, Feb 25, 2015 at 5:46 PM, Rob Schramm wrote: > If it is something smaller (not millions of rows), connect via JDBC and > use SQL Workbench/j. It has plenty of options to pull the data into a CSV. > > > > Rob Schramm > Senior Systems Consultant > > > On Tue, Feb 24, 2015 at 10:50 AM, Lizette Koehler > wrote: > >> Try adding more volumes. The DB2 List thinks you need to double what you >> are using with the JCL you posted. >> >> Lizette >> >> >> > -Original Message- >> > From: IBM Mainframe Discussion List [mailto:IBM-MAIN@LISTSERV.UA.EDU] >> > On Behalf Of Ron Thomas >> > Sent: Monday, February 23, 2015 7:44 AM >> > To: IBM-MAIN@LISTSERV.UA.EDU >> > Subject: Re: Unload DB2 table in CSV format >> > >> > Ok Thanks . I am getting the below message when i provided the DCB >> > Parameters >> > >> > "FMNBA324 A data set, or volume full condition occurred while >> attempting to >> > save this data set" >> > >> > >> > 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 DB2 table in CSV format
Try adding more volumes. The DB2 List thinks you need to double what you are using with the JCL you posted. Lizette > -Original Message- > From: IBM Mainframe Discussion List [mailto:IBM-MAIN@LISTSERV.UA.EDU] > On Behalf Of Ron Thomas > Sent: Monday, February 23, 2015 7:44 AM > To: IBM-MAIN@LISTSERV.UA.EDU > Subject: Re: Unload DB2 table in CSV format > > Ok Thanks . I am getting the below message when i provided the DCB > Parameters > > "FMNBA324 A data set, or volume full condition occurred while attempting to > save this data set" > > > 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 DB2 table in CSV format
I believe the DB2 Unload utility will also allow you to create an extract in a delimited form. Seems like there were some idiosyncrasies with it, but I don't recall the details at the moment. But you might start here: http://www-01.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/com.ibm.db2z10.doc.ugref/src/tpc/db2z_unloadsamples.dita Scott -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Unload DB2 table in CSV format
Is the maintenance for your file manager current? www-01.ibm.com/support/docview.wss?uid=swg21170609 To get a listing of current fixes for File Manager Have you opened a case with the vendor on this? Lizette > -Original Message- > From: IBM Mainframe Discussion List [mailto:IBM-MAIN@LISTSERV.UA.EDU] > On Behalf Of Ron Thomas > Sent: Monday, February 23, 2015 7:44 AM > To: IBM-MAIN@LISTSERV.UA.EDU > Subject: Re: Unload DB2 table in CSV format > > Ok Thanks . I am getting the below message when i provided the DCB > Parameters > > "FMNBA324 A data set, or volume full condition occurred while attempting to > save this data set" > > > 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 DB2 table in CSV format
This is not a question, I presume? Kees. -Original Message- From: IBM Mainframe Discussion List [mailto:IBM-MAIN@LISTSERV.UA.EDU] On Behalf Of Ron Thomas Sent: 23 February, 2015 15:44 To: IBM-MAIN@LISTSERV.UA.EDU Subject: Re: Unload DB2 table in CSV format Ok Thanks . I am getting the below message when i provided the DCB Parameters "FMNBA324 A data set, or volume full condition occurred while attempting to save this data set" 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 information, services and offers, please visit our web site: http://www.klm.com. This e-mail and any attachment may contain confidential and privileged material intended for the addressee only. If you are not the addressee, you are notified that no part of the e-mail or any attachment may be disclosed, copied or distributed, and that any other action related to this e-mail or attachment is strictly prohibited, and may be unlawful. If you have received this e-mail by error, please notify the sender immediately by return e-mail, and delete this message. Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its employees shall not be liable for the incorrect or incomplete transmission of this e-mail or any attachments, nor responsible for any delay in receipt. Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal Dutch Airlines) is registered in Amstelveen, The Netherlands, with registered number 33014286 -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Unload DB2 table in CSV format
Ok Thanks . I am getting the below message when i provided the DCB Parameters "FMNBA324 A data set, or volume full condition occurred while attempting to save this data set" 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 DB2 table in CSV format
Ron Thomas wrote: >//FMNOUT DD DSN=USMKT.SL2P.KUSMDW01.CUSSTO.F2B23.UNL1, >// UNIT=CART,VOL=(,RETAIN,,30),LABEL=01, >// DISP=(NEW,CATLG,CATLG) >FMNBA318 Output data set FMNOUT /USMKT.SL2P.KUSMDW01.CUSSTO.F2B23.UNL1 OPEN >failed ABEND code 013-34 . FMNDB801 Export function was canceled by the user, >or error encountered while prrocessing the copybook A quick look at 013 abend message, showed me there is something wrong with the missing DCB, possibly with BLKSIZE and LRECL. Please post it or refer to your product's documentation for a comparision. Since you have CATLG for abend situation, you can try out tapemap to see what the eventual DCB are there if that is written on your tape at all. But, you can lookup the correct DCB in the docs, include it in your JCL and re-submit your job after checking you DSN is not catalogued. DISCLAIMER: I'm not familiar with FMNDB2, but experience showed me if you don't get your output's attributes correct, you may get 'interesting' abends. 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 DB2 table in CSV format
You might also want to post this on the DB2 List. If you have not joined, you can do so at IDUG.ORG Lizette > -Original Message- > From: IBM Mainframe Discussion List [mailto:IBM-MAIN@LISTSERV.UA.EDU] > On Behalf Of Ron Thomas > Sent: Monday, February 23, 2015 6:55 AM > To: IBM-MAIN@LISTSERV.UA.EDU > Subject: Unload DB2 table in CSV format > > Hello. > > We are trying to unload the db2 table uusing file manager and creating a CSV > file, the below is the error we are getting. Could some one please help us > and let us know what could be the issue ? or is there any other way we can > download the file to a CSV format. > > //FMNDB2 EXEC PGM=FMNDB2,PARM=('SSID=DBKU,SQID=P4LX13V') > //STEPLIB DD DSN=SYS1.IFM.SFMNMOD1,DISP=SHR > // DD DSN=SYS5.DBGU.SDSNEXIT,DISP=SHR > // DD DSN=SYS5.DBGU.SDSNLOAD,DISP=SHR > //SYSPRINT DD SYSOUT=* > //FMNTSPRT DD SYSOUT=* > //SYSTERM DD SYSOUT=* > //FMNOUT DD DSN=USMKT.SL2P.KUSMDW01.CUSSTO.F2B23.UNL1, > // UNIT=CART,VOL=(,RETAIN,,30),LABEL=01, > // DISP=(NEW,CATLG,CATLG) > //SYSINDD * > $$FILEM DBX OBJIN="T23MDW01"."PUS_STO", > $$FILEM OUTPUT=FMNOUT, > $$FILEM NULLIND=_, > $$FILEM CSV=YES, > $$FILEM CSVHDR=YES, > $$FILEM DATAFORMAT=CSV, > $$FILEM SEPARATOR=',', > $$FILEM ROWS=ALL > > FMNBA318 Output data set FMNOUT > /USMKT.SL2P.KUSMDW01.CUSSTO.F2B23.UNL1 OPEN failed ABEND code > 013-34 . FMNDB801 Export function was canceled by the user, or error > encountered while prrocessing the copybook > > 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 DB2 table in CSV format
From the list of well-know errorcodes: 013-34: invalid blocksize. You should specify DCB info on the //FMNOUT dataset. Kees. -Original Message- From: IBM Mainframe Discussion List [mailto:IBM-MAIN@LISTSERV.UA.EDU] On Behalf Of Ron Thomas Sent: 23 February, 2015 14:55 To: IBM-MAIN@LISTSERV.UA.EDU Subject: Unload DB2 table in CSV format Hello. We are trying to unload the db2 table uusing file manager and creating a CSV file, the below is the error we are getting. Could some one please help us and let us know what could be the issue ? or is there any other way we can download the file to a CSV format. //FMNDB2 EXEC PGM=FMNDB2,PARM=('SSID=DBKU,SQID=P4LX13V') //STEPLIB DD DSN=SYS1.IFM.SFMNMOD1,DISP=SHR // DD DSN=SYS5.DBGU.SDSNEXIT,DISP=SHR // DD DSN=SYS5.DBGU.SDSNLOAD,DISP=SHR //SYSPRINT DD SYSOUT=* //FMNTSPRT DD SYSOUT=* //SYSTERM DD SYSOUT=* //FMNOUT DD DSN=USMKT.SL2P.KUSMDW01.CUSSTO.F2B23.UNL1, // UNIT=CART,VOL=(,RETAIN,,30),LABEL=01, // DISP=(NEW,CATLG,CATLG) //SYSINDD * $$FILEM DBX OBJIN="T23MDW01"."PUS_STO", $$FILEM OUTPUT=FMNOUT, $$FILEM NULLIND=_, $$FILEM CSV=YES, $$FILEM CSVHDR=YES, $$FILEM DATAFORMAT=CSV, $$FILEM SEPARATOR=',', $$FILEM ROWS=ALL FMNBA318 Output data set FMNOUT /USMKT.SL2P.KUSMDW01.CUSSTO.F2B23.UNL1 OPEN failed ABEND code 013-34 . FMNDB801 Export function was canceled by the user, or error encountered while prrocessing the copybook 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 information, services and offers, please visit our web site: http://www.klm.com. This e-mail and any attachment may contain confidential and privileged material intended for the addressee only. If you are not the addressee, you are notified that no part of the e-mail or any attachment may be disclosed, copied or distributed, and that any other action related to this e-mail or attachment is strictly prohibited, and may be unlawful. If you have received this e-mail by error, please notify the sender immediately by return e-mail, and delete this message. Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its employees shall not be liable for the incorrect or incomplete transmission of this e-mail or any attachments, nor responsible for any delay in receipt. Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal Dutch Airlines) is registered in Amstelveen, The Netherlands, with registered number 33014286 -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Unload DB2 table in CSV format
Hello. We are trying to unload the db2 table uusing file manager and creating a CSV file, the below is the error we are getting. Could some one please help us and let us know what could be the issue ? or is there any other way we can download the file to a CSV format. //FMNDB2 EXEC PGM=FMNDB2,PARM=('SSID=DBKU,SQID=P4LX13V') //STEPLIB DD DSN=SYS1.IFM.SFMNMOD1,DISP=SHR // DD DSN=SYS5.DBGU.SDSNEXIT,DISP=SHR // DD DSN=SYS5.DBGU.SDSNLOAD,DISP=SHR //SYSPRINT DD SYSOUT=* //FMNTSPRT DD SYSOUT=* //SYSTERM DD SYSOUT=* //FMNOUT DD DSN=USMKT.SL2P.KUSMDW01.CUSSTO.F2B23.UNL1, // UNIT=CART,VOL=(,RETAIN,,30),LABEL=01, // DISP=(NEW,CATLG,CATLG) //SYSINDD * $$FILEM DBX OBJIN="T23MDW01"."PUS_STO", $$FILEM OUTPUT=FMNOUT, $$FILEM NULLIND=_, $$FILEM CSV=YES, $$FILEM CSVHDR=YES, $$FILEM DATAFORMAT=CSV, $$FILEM SEPARATOR=',', $$FILEM ROWS=ALL FMNBA318 Output data set FMNOUT /USMKT.SL2P.KUSMDW01.CUSSTO.F2B23.UNL1 OPEN failed ABEND code 013-34 . FMNDB801 Export function was canceled by the user, or error encountered while prrocessing the copybook Thanks Ron T -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN