From: William M. Klein on
FYI,
"PIC G" (and DISPLAY-1) are still available, but certainly NOT what I would
use for IBM mainframe "migration".

I would use PIC N and USAGE NATIONAL.

However, I would still want to see how the files are defined on the mainframe
(either for DB2 or for COBOL).

As I read the information online at:
http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/dsnsqj11/2.9.4.4

and

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/dsnugj11/2.30.2.3.9

There really are LOTS of things to know about the unloaded mainframe DB2 table.
It appears as if the original data (GRAPHIC or VARGRAPHIC) can be either DBCS or
Unicode. It also appears that what the "delimiter" is (for Unloaded delimited
files) defaults differently depending on which it is - and that the person doing
the unload can specify what to use.

If the mainframe DB2 table includes ONLY "graphic" and "vargraphic" data (no
"single byte") info, then if the unload specified a DBCS or Unicode delimiter,
then the unloaded file SHOULD be able to be handled on AIX with a Micro Focus
file in which the FD file has ONLY "PIC N" data. If the X'0D' data is part of
such characters, I think it MIGHT be handled correctly. However, the MF
documentation does seem pretty clear that it strips out all X'0D' bytes for Line
Sequential files, so I am not at all certain this would work - unless you call
the file RECORD sequential (rather than line sequential).

***

It seems to me that there needs to be a LOT of research into how the DB2 table
and unloaded file are actually defined on the mainframe before a "good" solution
can be done on the PC. The copy directly of the table would seem to allow for
"proper" processing of the table(s) on the PC without conversion issues, but
even that would require knowing how the table data is actually defined (DBCS,
Unicode, whatever).

--
Bill Klein
wmklein <at> ix.netcom.com
"Robert" <no(a)e.mail> wrote in message
news:v8b294lklakslrjhj4l2g4fj22an4b97ge(a)4ax.com...
> On Thu, 31 Jul 2008 12:22:03 +1200, "Pete Dashwood"
> <dashwood(a)removethis.enternet.co.nz>
> wrote:
>
>>
>>
>>"Robert" <no(a)e.mail> wrote in message
>>news:hs8194lv6og5gvrbh58kv2andqrlbneql6(a)4ax.com...
>>> On Thu, 31 Jul 2008 03:05:12 +1200, "Pete Dashwood"
>>> <dashwood(a)removethis.enternet.co.nz>
>>> wrote:
>>>
>>>>
>>>>
>>>>"William M. Klein" <wmklein(a)nospam.netcom.com> wrote in message
>>>>news:IfXjk.339886$fz6.206173(a)fe08.news.easynews.com...
>>>>>I just want to repeat that if you have a mixture of EBCDIC and National
>>>>>(DBCS or Unicode) data in the DB2 table on the mainframe, that you will
>>>>>really need a LOT of information to be able to "correctly" migrate this
>>>>>to
>>>>>AIX.
>>>>
>>>>That may not be the case, Bill. There has been no suggestion that a
>>>>MIXTURE
>>>>is in use.
>>>>
>>>>Taoxianfeng said they are on a straight Japanese code page.
>>>>
>>>>If the AIX machine can recognise the DBCS, it should all be fine. DBCS is
>>>>like Unicode (inasmuch as it comes in standard "flavours"); it's a
>>>>standard
>>>>format for any platform that supports IBM's DBCS.
>>>>
>>>>(I had to use it once many years ago and have never forgotten the
>>>>experience...)
>>>>
>>>>Quoting from IBM:
>>>>
>>>>The IBM-932 code page (Japanese) is one example of a DBCS code page in
>>>>which:
>>>>X'00' to X'7F' are single-byte codes
>>>>X'81' to X'9F' are double-byte introducer
>>>>X'A1' to X'DF' are single-byte codes
>>>>X'E0' to X'FC' are double-byte introducer
>>>>
>>>>(No wonder the stripped out x'0D's are problematic... :-))
>>>>
>>>>I think the problem is arising because the CONTAINER for the data is a
>>>>LINE
>>>>SEQUENTIAL file, which happens to use x'0D' for a special purpose.
>>>>
>>>>The data is there, because when it is HEX encoded it arrives at the AIX
>>>>machine correctly.
>>>>
>>>>If the data is converted back to the original, and the AIX machine is also
>>>>running the Japanese code page, I believe everything will work correctly.
>>>>
>>>>It isn't the difference in OS that is the problem, it is the transport
>>>>layer. (Rick's suggestion to HEX encode it solves this nicely.)
>>>
>>> I think the problem is that Micro Focus does not know the input file
>>> contains DBCS codes.
>>> It is treating the input as single-byte US-ASCII.
>>
>>It certainly looks that way.
>>
>>>You tell it about the codeset using the
>>> codecomp program to create a codeset file and the MFCODESET environment
>>> variable to tell
>>> it to use that page at execution time.
>>>
>>> http://supportline.microfocus.com/supportline/documentation/books/sx22sp1/sx22indx.htm
>>
>>So, are you saying Robert, that if Taoxianfeng simply sets the MFCODESET EV
>>to point at the Japanese code page, at the time he runs his MF COBOL
>>program, it should work? Definitely worth a try...
>
> Yes. Add compiler option DBCS, and pic G instead of pic X. There is also an
> environment
> variable LANG, but it apears to be for NLS (Latin alphabet).
>
> The manual talks about calling _CODESET to translate EBCDIC DBCS to ASCII
> DBCS. I'm
> guessing that involves quotes, commas and other punctuation characters. If
> embedded 0D and
> others below x'20' go away, the need for EBCDIC-ASCII translation should be
> evident from
> viewing the output file.
>


From: taoxianfeng on
On 7月31日, 午後1:16, Robert <n...(a)e.mail> wrote:
> On Wed, 30 Jul 2008 19:02:37 -0700 (PDT), taoxianf...(a)gmail.com wrote:
> >On 7¤ë31¤é, ¤È«e2:31, "Frank Swarbrick" <Frank.Swarbr...(a)efirstbank.com>
> >wrote:
> >> One thing I think has been mentioned in passing but perhaps overlooked is
> >> doing a database to database load, rather than an export followed by an
> >> import.  I have only been partially paying attention, but is the goal to get
> >> data that currently exists in DB2 for z/OS in to a DB2 AIX database?  If you
> >> have DB2 9.1 or 9.5 on AIX you should be able to do something like the
> >> following:
>
> >> DECLARE load_curs CURSOR
> >>     DATABASE <sourcedb>
> >>     USER <source_db_user_name>
> >>     USING <source_db_user_password>
> >>     FOR SELECT * FROM <source_table_name>;
> >> LOAD FROM load_curs OF CURSOR
> >>     REPLACE INTO <dest_table_name>;
>
> >> I believe this feature (the DATABASE/USER/USING clauses) is available only
> >> in version 9.  With prior versions you have to set up "data federation" on
> >> your destination db and have nicknames for your source db tables.  More
> >> complicated, but still possible.  Either way your AIX database must be able
> >> to connect to your z/OS database.  If that is not an option then this will
> >> not work.
>
> >> I have no idea if this actually meets your requirements, but it's one
> >> possible option.  No Cobol needed, and no export files needed.
>
> >> Frank
>
> >Do you mean load data from mainframe DB to AIX DB directly if they can
> >be connected?
> >Yes there would be problems when migrating the data. But now what I
> >need to do is like that:
>
> >export 2 tables (or some fields of it);
> >sort the exported data by mfsort;
>
> The database will sort them if you say ORDERED BY.
>
> >match the sorted file and only output the necessary records;
>
> That's a simple database join operation. There is no need to sort the tables.
>
> create table temp_a as
>   select a.* from a, b
>     where a.customer_id = b.customer_id;
>
> or
>
> create table temp_a as
>   select * from a
>     where customer_id in (select customer_id from b);
>
> >import the output file back to the table replacing the old ones.
>
> drop table a;
> rename table temp_a to a;
>
> >The original sources are JCL and mainframe cobol which are to be
> >migrated to AIX shell and microfocus cobol.
> >I'm sorry I describe this whole image so late that maybe misleaded you
> >all.
>
> >Actually it also maybe possible to read the 2 tables in the cobol
> >program and delete the unmatched ones(almost writing a new source).
> >But I think it costs too much. Perhaps it's the last method we will
> >try.
>
> You might be doing it the hard way.- 引用テキストを表示しない -
>
> - 引用テキストを表示 -

Well, I also know the basic SQL such as ORDER BY and JOIN.

But it's not me who chose to handle these data by export-match-import.

Just as I said before, it would be easier with some shell script but
it's not up to me.
From: Anonymous on
In article <effb0602-5b9b-44cc-a258-12880abe9038(a)q5g2000prf.googlegroups.com>,
<taoxianfeng(a)gmail.com> wrote:
>On 7月31日, 午後1:16, Robert <n...(a)e.mail> wrote:

[snip]

>> You might be doing it the hard way.- 引用テキストを表示しない -
>>
>> - 引用テキストを表示 -
>
>Well, I also know the basic SQL such as ORDER BY and JOIN.
>
>But it's not me who chose to handle these data by export-match-import.

It seems that your knowledge of the tools required (COBOL and SQL) is
rather basic and the choice of solution is being dictated elsewhere.

Either you are receiving some very expensive on-the-job training or your
management deserves the results they are getting.

DD

From: taoxianfeng on
On 7$B7n(B31$BF|(B, $B8a8e(B8:32, docdw...(a)panix.com () wrote:
> In article <effb0602-5b9b-44cc-a258-12880abe9...(a)q5g2000prf.googlegroups.com>,
>
> <taoxianf...(a)gmail.com> wrote:
> >On 7$B7n(B31$BF|(B, $B8a8e(B1:16, Robert <n...(a)e.mail> wrote:
>
> [snip]
>
> >> You might be doing it the hard way.- $B0zMQ%F%-%9%H$rI=<($7$J$$(B -
>
> >> - $B0zMQ%F%-%9%H$rI=<((B -
>
> >Well, I also know the basic SQL such as ORDER BY and JOIN.
>
> >But it's not me who chose to handle these data by export-match-import.
>
> It seems that your knowledge of the tools required (COBOL and SQL) is
> rather basic and the choice of solution is being dictated elsewhere.
>
> Either you are receiving some very expensive on-the-job training or your
> management deserves the results they are getting.
>
> DD

You got it. I'm a pure novice who just graduated from university last
year,especially compared to the ones who replied my post with decades
of experiences.
You can also evaluate me and my company as you wish. Even myself is
thinking about 2 things:
1.the organization is really stupid;
2.try to find another job.

BUT THAT'S ANOTHER QUESTION.

I just want to find a technical solution here and try to do as much as
I can. And I'm not posting with some silly questions or doing nothing
but waiting for answer.

You may guess I'm Chinese from my mail address. We have an old saying
"Do one's best and leave the rest to God's will".
From: William M. Klein on
Coming up with another approach entirely to the problem. (I know Micro Focus
better on Windows than AIX, so this MAY take more work on AIX than it would on
Windows).

It seems to me that some (possibly most) of the problems come from the
"download" process of the unloaded file being created as "line sequential". My
assumption is that on the mainframe it is a DCB=RECFM=VB (i.e. "variable length
QSAM) file. If so, then it may be worth looking into the Micro Focus "VRECGEN"
facility. Run some jobs (on the mainframe and the AIX environment), it might be
possible to get a
Record Sequential - Variable Length
file on AIX - with ALL the data "as it is on the mainframe".

I am NOT saying that this is the "best" solution (or the only solution), but it
might be the easiest - if the "mandate" has come in that the file must be a DB2
unloaded file from the mainframe that is downloaded to AIX and processed there
as a file that is THEN loaded into an AIX table.

--
Bill Klein
wmklein <at> ix.netcom.com