IBM i Global

 View Only
Expand all | Collapse all

Data Extractions From DB2 with i / AS400 V7 R3

  • 1.  Data Extractions From DB2 with i / AS400 V7 R3

    Posted Wed March 29, 2023 09:36 AM

    Hi, 

    I want to ask about data extraction on DB2 with i/AS400 V7 R3. how to retrieve data to plat file output .dat and ASCII Files format. Maybe there are some who have experience with it? 

    Example in ASCII should be like this:


    1 {TAB} {x'1D'} AAAA {x'1D} {TAB} {x'1D'}xxxx-xx-xx{x'1D'} {CR} {LF}


    Thanks in advance, hopefully someone can help.



    ------------------------------
    Ari Takalam
    ------------------------------


  • 2.  RE: Data Extractions From DB2 with i / AS400 V7 R3

    IBM Champion
    Posted Wed March 29, 2023 08:03 PM
    Edited by Satid Singkorapoom Wed March 29, 2023 08:08 PM

    Dear Ari

    One choice you can consider is IBM i data export/import command CPYTOIMPF/CPYFRMIMPF.   In your case, use CPYTOIMPF to export from a physical file to a stream file and specify proper values for the parameter

    • FROMCCSID(37) - 37 is CCSID for EBCDIC English. If this is not the case for you, Google with "ibm ccsid" to find a proper value. 
    • STMFCCSID(437)  - 437 is CCSID for ASCII US English - or use *PCASCII
    • RCDDLM(*CRLF)
    • FLDDLM(*TAB)
    • DATFMT - values available are *ISO, *USA, *EUR, *JIS, *YYMD

    You may want to explore other command parameters as well : https://www.ibm.com/docs/en/i/7.3?topic=ssw_ibm_i_73/cl/cpytoimpf.html

    This IBM Technote provides some examples : https://www.ibm.com/support/pages/cpytoimpf-and-cpyfrmimpf-examples

    Do a Google searh with "ibm i cpytoimpf" and you will find plenty information including some Youtube videos. 

    Once the resulting stream file is created in IBM i IFS directory, you get it by mapping Windows client network drive to IFS or use Navigator for i GUI to download it to your PC. 



    ------------------------------
    Education is not the learning of facts but the training of the mind to think. -- Albert Einstein.
    ------------------------------
    Satid S.
    ------------------------------



  • 3.  RE: Data Extractions From DB2 with i / AS400 V7 R3

    IBM Champion
    Posted Thu March 30, 2023 10:09 AM

    If you need more flexibility than offered by CPYTOIMPF you should explore using an Open Access Handler. Using that does away with the need to produce the intermediate file that is used to feed CPYTOIMPF. Artciles here describe the process and provide a basic handler. https://authory.com/JonParisAndSusanGantner?collection=Open-Access

    In particular this article https://authory.com/JonParisAndSusanGantner/Open-Access-IFS-Handler-Revisited and this one may also be useful in the future - if anyone ever asks you for a fixed format file (which CPYTOIMPF can't do) https://www.itjungle.com/2019/02/11/guru-open-access-to-the-rescue/




    ------------------------------
    Jon Paris
    ------------------------------



  • 4.  RE: Data Extractions From DB2 with i / AS400 V7 R3

    Posted Fri March 31, 2023 08:46 AM

    By the way: if you need to replicate changes in tables near-real-time, you can think on an IBM product called Infosphere Data Replication, that has a very low footprint and is able to take changes from journaling and transmit only those records that changed.



    ------------------------------
    Daniel Jose Lema Guanziroli
    ------------------------------



  • 5.  RE: Data Extractions From DB2 with i / AS400 V7 R3

    IBM Champion
    Posted Fri March 31, 2023 08:28 PM

    Dear Daniel

    Forgive me for asking if you posted in a wrong thread? Your post does not seem to be relevant to the topic of this thread. 



    ------------------------------
    Education is not the learning of facts but the training of the mind to think. -- Albert Einstein.
    ------------------------------
    Satid S.
    ------------------------------



  • 6.  RE: Data Extractions From DB2 with i / AS400 V7 R3

    Posted Mon April 03, 2023 05:20 PM

    Why do you think so? 

    if Mr/Mrs @Ar T is surveying different alternatives to move data from/to IBM i, it is perfectly reasonably to explore CPYFRMIMPF/CPYTOIMPF as a great method for extracting/loading data massively from IBM i, but it is also valid to consider other type of data replication options such as those based on change data capture tools based on journal content, which are appropriate for maintaining synchronization between large tables and that are able to support multivendor RDBMS.  In fact, it is a great strategy to combine both methods, one for initial replication (or resynchronization) and the other for maintaining them synchronized.

    I have been working with IBM i shops (as well as many other RDBMS) since 1988 and I have been using Import/Export options, as well as ETL like, and change data capture methods for data replication.



    ------------------------------
    Daniel Jose Lema Guanziroli
    ------------------------------



  • 7.  RE: Data Extractions From DB2 with i / AS400 V7 R3

    IBM Champion
    Posted Fri March 31, 2023 07:27 AM

    If you do WRKPTFGRP and you see SF99703 at level 22 or higher then you could use one of the functions here:
    https://www.ibm.com/docs/en/i/7.3?topic=is-ifs-write-ifs-write-binary-ifs-write-utf8-procedures



    ------------------------------
    Robert Berendt IBMChampion
    ------------------------------



  • 8.  RE: Data Extractions From DB2 with i / AS400 V7 R3

    IBM Champion
    Posted Fri March 31, 2023 08:34 PM

    Dear Robert

    Forgive me for also asking if you posted in a wrong thread? Your post does not seem to be relevant to the topic of this thread.  Or is there something abnormal in this community's posting mechanism?



    ------------------------------
    Education is not the learning of facts but the training of the mind to think. -- Albert Einstein.
    ------------------------------
    Satid S.
    ------------------------------



  • 9.  RE: Data Extractions From DB2 with i / AS400 V7 R3

    IBM Champion
    Posted Mon April 03, 2023 07:25 AM

    Nope, I pointed exactly to the right thread.



    ------------------------------
    Robert Berendt IBMChampion
    ------------------------------



  • 10.  RE: Data Extractions From DB2 with i / AS400 V7 R3

    Posted Mon April 03, 2023 08:49 AM

    Satid,
    Why do you keep posting this? Both posts you commented on were on the correct thread and relevant. 



    ------------------------------
    Jeremy Bowling
    ------------------------------



  • 11.  RE: Data Extractions From DB2 with i / AS400 V7 R3

    IBM Champion
    Posted Mon April 03, 2023 07:34 PM
    Edited by Satid Singkorapoom Mon April 03, 2023 09:07 PM

    Dear Jeremy and Daniel

    I just did not understand how data replication solution can be involved here because I see there are EBCDIC to ASCII and data format conversion to be done and I have no idea Infosphere data replication can do this. So, I could not understand its relevancy here and therefore my question. If Infosphere is capable of ETL capability and Daniel originally mentioned it, I would have got the point.  



    ------------------------------
    Education is not the learning of facts but the training of the mind to think. -- Albert Einstein.
    ------------------------------
    Satid S.
    ------------------------------



  • 12.  RE: Data Extractions From DB2 with i / AS400 V7 R3

    Posted Tue April 04, 2023 11:23 AM

    Thanks for asking:

    Most ETL tools support EBCDIC to ASCII and ASCII to EBCDIC conversion out of the box. It is more difficult to get support to multimember files (tables).

    The main CDC tools available in the market supports EBCDIC <--> ASCII (and some versions of UTF) too.  In my opinion, the CDC tool that best supports IBM i is Infosphere Data Replication, not because of EBCDIC, but because it supports specifics of IBM i such as multimember physical files (with some limitations), and remote journaling <-- but keep in mind that CDC is great for keeping tables synchronized and for multivendor near-real time data integration is wonderful (and for maintain sync with data on cloud), but not for bulk data movements.  It is also a product with a significative cost.

    For bulk data movements, CPYFRMIMPF/CPYTOIMPF is a great tool and supports parallel processing, which makes it very fast (I don't remember if SMP has a fee on IBM i).  The issue with the original question is that some fields seem to be separated by three characters, {TAB} {x'1D'} (including the white in the middle of {TAB] and {x'1D}), while others are separated only by one or two {x'1D'} (blanks before and after that {x'1D}). That is not supported by CPYFRMIMPF/CPYTOIMPF, but I think that issue can be fixed by some PASE (UNIX like) command such as sed or awk in just one statement.



    ------------------------------
    Daniel Jose Lema Guanziroli
    ------------------------------



  • 13.  RE: Data Extractions From DB2 with i / AS400 V7 R3

    IBM Champion
    Posted Wed April 05, 2023 02:51 AM
    Edited by Satid Singkorapoom Wed April 05, 2023 03:28 AM

    Dear Daniel

    >>>> Most ETL tools support EBCDIC to ASCII and ASCII to EBCDIC conversion out of the box. It is more difficult to get support to multimember files (tables). <<<<

    Have you ever heard of SQL Alias object in DB2 for i (it is also called DDM File in native IBM i jargon)?  For any SQL access to Db2i multi-member tables (native SQL and remote interface such as DRDA, ODBC, JDBC), you can use an SQL Alias to accommodate such an access.  Each SQL Alias object is conveniently created (CREATE ALIAS ....or CL command CRTDDMF) for each physical file member (no need for the first member, of course).  Then you run SQL against an Alias as if it were a normal SQL table.

    As for Changed Data Capture tool in IBM i, I notice that it has been less and less of use for DB2 for i since the availability of Automatic Timestamp support delivered in DB2 for i release 6.1 some 12 years ago.  With this capability, anyone can add a normal or hidden timestamp column to an existing table and then write SQL statements to do CDC task utilizing SQL date-time arithmetic function.  On-line CDC task needs a more sophisticated SQL coding than batch CDC.  But I see that a CDC tool with the additional capability for EBCDIC to ASCII/Unicode and column format conversion can still be of value to customers who have such a need. By based on my work experience, I observed there were only very few IBM i customers with such an additional requirement in my part of the globe



    ------------------------------
    Education is not the learning of facts but the training of the mind to think. -- Albert Einstein.
    ------------------------------
    Satid S.
    ------------------------------



  • 14.  RE: Data Extractions From DB2 with i / AS400 V7 R3

    Posted Wed April 05, 2023 03:40 PM

    Good day Mr. Satid:

    I don't know if a chain of answers can be splitted as a main subject in this forum tool and I found that point regarding Aliases very interesting.

    Years ago (may be in 7.1 or 7.2) I tried to work with aliases but I experience two issues that I do not know if were already solved:

    • I need to create an alias to the last member.  The command allows to create alias for a specific member or for the *first member, but do not support *LAST member.  I develop a procedure to get the list of members to choose the right one, but that is an extra complexity that can be avoided if the CREATE ALIAS command allows *LAST as an option. It seems to be easy to implement for the lab guys, but maybe they decided not to do it to discourage the usage of Members.
    • The other issue was that I tried to create an alias for each member and then, create a view with <emphasis>select-union all-select...</emphasis> to see them together, but for some reason, IBM i SQL does not allow to create a view that perform union or union all on several aliases on the same table, or at least in that version.

    Assertions have expiration date. I don't know if this has been changed since then.



    ------------------------------
    Daniel Jose Lema Guanziroli
    ------------------------------



  • 15.  RE: Data Extractions From DB2 with i / AS400 V7 R3

    IBM Champion
    Posted Wed April 05, 2023 03:56 PM

    Here is something which has changed over time.  Talk to your BP and see if they can add the, now, no charge DB2 Multisystem code.  Formerly it used to cost you your first born male child.  If you can find something to automatically tie members together you can query multiple members as one.  For example, let's say you have an order table.  Now let's say you have an order date column in that table.  Let's say you want to partition (member) it by the year of the order date.  Quite Easily Done.  You simply use the partition clause on the create table statement.
    I'm still trying to get that code from my BP and I'd like to try this.



    ------------------------------
    Robert Berendt IBMChampion
    ------------------------------



  • 16.  RE: Data Extractions From DB2 with i / AS400 V7 R3

    IBM Champion
    Posted Wed April 05, 2023 08:13 PM
    Edited by Satid Singkorapoom Wed April 05, 2023 08:14 PM

    Dear Daniel

    For your first question, SQL CREATE ALIAS supports the specification of member name only.   As for CRTDDMF, I just find it does not support a local file as it has no parameter for member name. This may be a change that I am not aware of or it may not support local file from the beginning and I may have a misunderstanding on this. 

    For your 2nd question, I have no answer but I can say that if you encountered this issue a long time ago, there is a chance that the issue may already be addressed by certain DB2i enhancement in the recent releases. The best thing to get an answer is to test it again. 

    Since DB2 Multisystem product is now free of charge for all releases of IBM i version 7 (but you need to have an active SWMA to get its license key), you can see it as a new option to get to all data in multi-member physical file with what is called Table Partitioning  There is a fresh article discussing this and just published a few days ago that you can read here : https://powerwire.eu/db2-for-i-multisystem/.  And here is another one talking about expanding the max size of a table but I understand that you can use it for multi-member access without having to wait for the file to reach its max size limit : Push IBM i beyond the limits – table partitioning at https://community.ibm.com/community/user/power/blogs/nicolae-chirea/2022/08/26/push-ibm-i-beyond-the-limits. It appaers that multi-member data access through SQL is now much more convenient by the help from DB2 Multisystem product.



    ------------------------------
    Education is not the learning of facts but the training of the mind to think. -- Albert Einstein.
    ------------------------------
    Satid S.
    ------------------------------