From: Robert on 29 Jul 2008 09:05
On Mon, 28 Jul 2008 22:28:35 -0700 (PDT), taoxianfeng(a)gmail.com wrote:
>On Jul 29, 12:19�pm, Robert <n...(a)e.mail> wrote:
>> On Mon, 28 Jul 2008 18:23:18 -0700 (PDT), taoxianf...(a)gmail.com wrote:
>> >On Jul 29, 2:25�am, "Rick Smith" <ricksm...(a)mfi.net> wrote:
>> >> <taoxianf...(a)gmail.com> wrote in message
>> >> [snip]
>> >> > BUT now I'm just writing a pure cobol source matching some "special"
>> >> > text file.
>> >> > You can do a experiment to see how is a varchar field exported(into
>> >> > DEL format) - only the characters.
>> >> > I can't show my data but some records contain no 0D and some have lots
>> >> > of 0D spreading the whole varchar field.
>> >> [snip]
>> >> A bit of research (google using: site:ibm.com db2 +aix +hex)
>> >> shows that a HEX function is available (in DB2 V9) that may be
>> >> used during EXPORT to convert the characters in the VARCHAR
>> >> column to hex display. Using this function would seem to simplifiy
>> >> the problem with having control character values as data.
>> >Well, it's really a fantastic method! All data is successfully
>> >There's only one more thing:
>> >I need to import the matched data back to the table, so I'm searching
>> >the way to convert the HEX displayed varchar column back. Would you
>> >mind give me a bit more hint?
>> Code Page 1252 is hex
>> �load from filename of del modified by codepage=1252- Hide quoted text -
>> - Show quoted text -
>The DB2 codepage is set to IBM-943 (Japanese) so a SQL2754N "cannot be
>converted" error happens when trying to load data with codepage 1252.
>Maybe I should change the DB codepage?
My mistake. Code page 1252 converts only to US-ASCII. Japanese explains why you couldn't
show the data here.
From: Robert on 30 Jul 2008 23:59
On Wed, 30 Jul 2008 19:46:02 -0700 (PDT), taoxianfeng(a)gmail.com wrote:
>On 7?31?, ??2:27, Robert <n...(a)e.mail> wrote:
>> On Thu, 31 Jul 2008 03:05:12 +1200, "Pete Dashwood" <dashw...(a)removethis.enternet.co.nz>
>> >"William M. Klein" <wmkl...(a)nospam.netcom.com> wrote in message
>> >>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
>> >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
>> >Quoting from IBM:
>> >The IBM-932 code page (Japanese) is one example of a DBCS code page in
>> >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. 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/sx2...- ???????????? -
>> - ????????? -
>Thank you Robert.
>I tried exporting MFCODESET ev directly then recompile and run the
>program. But it gives nothing different.
It appears that compiler option NLS is required to use a codeset at execution time. I
thought it was DBCS, but now see that applies to DBCS in source code at compile time, not
in data files at execution time.
Also, define the record with DISPLAY-1 PIC G, which tells it to allocate two bytes per
>(referencing the codeset No. from http://www.microfocus.com/000/20031001_003_tcm21-6159.pdf
>, I used 0081,0939 and 9122)
>So I'm trying to use the codecomp utility to build new mapping now.
>However, I feel that it's all about ASCII and EBCDIC since the
>document says "The Codecomp utility enables you to reconfigure the
>_CODESET program for single-byte characters."
I would first try to get rid of control characters less than x'20'. If strings in the
output look like valid katakana, THEN deal with ASCII-EBCDIC. If strings are not bounded
by quotes, that would be a sure sign you need translation, the bounding character must be
an untranslated EBCDIC quote.
If you DO need to convert, see 8.11.4 function 4 here:
From: Robert on 31 Jul 2008 00:16
On Wed, 30 Jul 2008 19:02:37 -0700 (PDT), taoxianfeng(a)gmail.com wrote:
>On 7��31��, �ȫe2:31, "Frank Swarbrick" <Frank.Swarbr...(a)efirstbank.com>
>> 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
>> 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.
>Do you mean load data from mainframe DB to AIX DB directly if they can
>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;
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
>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
You might be doing it the hard way.
From: Robert on 31 Jul 2008 09:02
On Thu, 31 Jul 2008 11:32:41 +0000 (UTC), docdwarf(a)panix.com () wrote:
>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:
>>> 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.
In the brave new world of contract programming, unreasonable assignments are semi-common.
I was once assigned to write a new program from scratch in a language (VB) I barely knew,
and given three days to get it done. It was a non-trivial program and there was no model I
could use as a starting point. If I hadn't gotten it done, there was an implied threat of
unemployment. I've seen worse, for example people assigned to work on huge
mission-critical mainframe assembly language programs with two weeks' training in Cobol
and no clue about assembly language on any machine. In that instance, three out of four
'failed' and were fired. The contracting company just shoveled more bodies onto the fire.
There's no time or budget for 'professionalism' in this world. You get it done, or else.
From: Anonymous on 31 Jul 2008 09:28
In article <fhc3949gck3o6sp2p7rlh7echnbe9opbkt(a)4ax.com>,
Robert <no(a)e.mail> wrote:
>On Thu, 31 Jul 2008 11:32:41 +0000 (UTC), docdwarf(a)panix.com () wrote:
>>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:
>>>> 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.
>In the brave new world of contract programming, unreasonable assignments
Is that so, Mr Wagner? Gosh and golly gee, one learns something new every
>I was once assigned to write a new program from scratch in a language
>(VB) I barely knew,
>and given three days to get it done. It was a non-trivial program and
>there was no model I
>could use as a starting point. If I hadn't gotten it done, there was an
>implied threat of
In the few short decades that I've been working as a
consultant/contractor/hired gun I have been given such assignments; my
response has been to state, in writing, that I have not, at any time,
presented myself as already possessing the skills which the task appears
to require and I cannot, in good professional faith, provide the same
degree of certainty for the quality of product that will result.
In short: 'I am more than willing to give it a shot but I never said that
I have done this sort of stuff before. If it all goes kerflooie then it
all goes kerflooie.'
>I've seen worse, for example people assigned to work on huge
>mission-critical mainframe assembly language programs with two weeks'
>training in Cobol
>and no clue about assembly language on any machine. In that instance,
>three out of four
>'failed' and were fired. The contracting company just shoveled more
>bodies onto the fire.
The contracting company seems to have been aware that their status was not
threatened by supplying inferior product. If the person with
signing-authority over the contract keeps pumping out checks for
low-quality personnel then... it appears that checks keep getting signed.
>There's no time or budget for 'professionalism' in this world. You get
>it done, or else.
I have no idea what 'professionalism' is in your book, Mr Wagner. As
noted above I will put in writing what I have done before and what I'm
willing to do now; if the client disapproves of this... well, there's
always another job.