Re: All X'0D' lost during reading line sequential file usingmicrofocus se

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

On Wed, 30 Jul 2008 11:31:38 -0600, "Frank Swarbrick" <Frank.Swarbrick@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.

Oracle is simpler

create table <dest> as select * from <src>@<sourcedb>;

where sourcedb is defined once:

create database link <sourcedb>
connect to <user> identified by <password> using 'service handle';

If you don't have permission to create a db link, you can use the sqlplus copy command:

copy from <user>/<password>@<sourcedb> create <dest> using select * from <src>;

On 7¤ë31¤é, ¤È«e2:31, "Frank Swarbrick" <Frank.Swarbr...@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;
match the sorted file and only output the necessary records;
import the output file back to the table replacing the old ones.

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.

On Wed, 30 Jul 2008 19:02:37 -0700 (PDT), taoxianfeng@gmail.com wrote:

>On 7¤ë31¤é, ¤È«e2:31, "Frank Swarbrick" <Frank.Swarbr...@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.

On 7月31日, �後1:16, Robert <n...@e.mail> wrote:
> On Wed, 30 Jul 2008 19:02:37 -0700 (PDT), taoxianf...@gmail.com wrote:
> >On 7¤ë31¤é, ¤È«e2:31, "Frank Swarbrick" <Frank.Swarbr...@efirstbank.com>
> >wrote:
> >> One thing I think has been mentioned in passing but perhaps overlookedis
> >> 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.

In article <effb0602-5b9b-44cc-a258-12880abe9038@q5g2000prf.googlegroups.com>,
<taoxianfeng@gmail.com> wrote:
>On 7月31日, �後1:16, Robert <n...@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

On Thu, 31 Jul 2008 11:32:41 +0000 (UTC), docdwarf@panix.com () wrote:

>In article <effb0602-5b9b-44cc-a258-12880abe9038@q5g2000prf.googlegroups.com>,
> <taoxianfeng@gmail.com> wrote:
>>On 7月31日, �後1:16, Robert <n...@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.

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.

In article <fhc3949gck3o6sp2p7rlh7echnbe9opbkt@4ax.com>,
Robert <no@e.mail> wrote:
>On Thu, 31 Jul 2008 11:32:41 +0000 (UTC), docdwarf@panix.com () wrote:
>
>>In article <effb0602-5b9b-44cc-a258-12880abe9038@q5g2000prf.googlegroups.com>,
>> <taoxianfeng@gmail.com> wrote:
>>>On 7月31日, �後1:16, Robert <n...@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.
>
>In the brave new world of contract programming, unreasonable assignments
>are semi-common.

Is that so, Mr Wagner? Gosh and golly gee, one learns something new every
day.

>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.

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.

DD

On 7$B7n(B31$BF|(B, $B8a8e(B8:32, docdw...@panix.com () wrote:
> In article <effb0602-5b9b-44cc-a258-12880abe9...@q5g2000prf.googlegroups.com>,
>
> <taoxianf...@gmail.com> wrote:
> >On 7$B7n(B31$BF|(B, $B8a8e(B1:16, Robert <n...@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".

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