Original Message:
Sent: Wed April 05, 2023 03:39 PM
From: Daniel Jose Lema Guanziroli
Subject: Data Extractions From DB2 with i / AS400 V7 R3
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 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
Original Message:
Sent: Wed April 05, 2023 02:50 AM
From: Satid Singkorapoom
Subject: Data Extractions From DB2 with i / AS400 V7 R3
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.
Original Message:
Sent: Tue April 04, 2023 11:22 AM
From: Daniel Jose Lema Guanziroli
Subject: Data Extractions From DB2 with i / AS400 V7 R3
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
Original Message:
Sent: Mon April 03, 2023 07:33 PM
From: Satid Singkorapoom
Subject: Data Extractions From DB2 with i / AS400 V7 R3
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.
Original Message:
Sent: Mon April 03, 2023 08:49 AM
From: Jeremy Bowling
Subject: Data Extractions From DB2 with i / AS400 V7 R3
Satid,
Why do you keep posting this? Both posts you commented on were on the correct thread and relevant.
------------------------------
Jeremy Bowling
Original Message:
Sent: Fri March 31, 2023 08:33 PM
From: Satid Singkorapoom
Subject: Data Extractions From DB2 with i / AS400 V7 R3
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.
Original Message:
Sent: Fri March 31, 2023 07:26 AM
From: Robert Berendt
Subject: Data Extractions From DB2 with i / AS400 V7 R3
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
Original Message:
Sent: Tue March 28, 2023 08:47 PM
From: Ar T
Subject: Data Extractions From DB2 with i / AS400 V7 R3
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
------------------------------