IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

VSAM to DB2 Migration


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Tue Jan 24, 2012 3:34 pm
Reply with quote

Hi,

While doing a migration from VSAM to DB2.
I am planning to write a rexx script to convert a COBOL copybook for the VSAM file to table DDL.

Can you please help me with your valuable inputs.

Or is there any tool/Script available?

Thanks in Advance.
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Tue Jan 24, 2012 4:59 pm
Reply with quote

Try asking yourself

1.How would you handle structures in the copybook
2.Redefines
3.How would I differentiate not null or not null with default
4.On what basis you create the PRIMARY KEY,FOREIGN KEY,UNIQUE contraints
5.Checks

These points might have given you some alarm icon_biggrin.gif may be experts might have much more to say
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Tue Jan 24, 2012 5:05 pm
Reply with quote

I just want to create a generic DDL.

Do not want to include constraints and primary keys....
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Wed Jan 25, 2012 4:41 pm
Reply with quote

Hi,

Is there any document where the Rules for defining a cobol copybook are defined?
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10886
Location: italy

PostPosted: Wed Jan 25, 2012 4:48 pm
Reply with quote

Quote:
Is there any document where the Rules for defining a cobol copybook are defined?


after 2 and a half years You should not have the need to be told that the COBOL and DB2 manuals will give You all the info You need

anyway apart the more complicated COBOL declares ( occurs, redefines )
even converting a basic
xx <varname> <datatype>

is not that simple
You will need to parse the datatype
and use the info provided here
publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=%2Frzajp%2Frzajpequivsqlcobol.htm
to generate the proper sql column definition
and beware that, IIRC, while "-" is valid char for a cobol variable name it is not so for a DB2 column name


not easy if You are not pretty proficient in rexx
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Wed Jan 25, 2012 5:29 pm
Reply with quote

Thanks Enrico...

Any ideas on how to go about it?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Jan 25, 2012 5:44 pm
Reply with quote

DFSORT has a REXX that converts copybooks containing structures
(remember, a COBOL copybook can contain any legal COBOL statement:
data definitions, comments, code)
into SYSNAMES.

That could be a starting point.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Jan 25, 2012 5:51 pm
Reply with quote

i, personally, would create one cobol module,
and put each copybook (which contains a structure)
in the linkage section,
then run a syntax compile.

this would generate (in the xref)
the datadefinitions, with type, length and location within the structure
thus saving you the problem of creating the logic to properly interpret the stucture.

not even going to comment on the fact that it seems that your vsam to db2 conversion
appears to be only using vsam records to create a row.
thus each vsam record type would be a table defintion.

no, i am going to comment: dumb.
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Wed Jan 25, 2012 7:14 pm
Reply with quote

dbzTHEdinosauer wrote:
DFSORT has a REXX that converts copybooks containing structures
(remember, a COBOL copybook can contain any legal COBOL statement:
data definitions, comments, code)
into SYSNAMES.

That could be a starting point.


Hi,

When the REXX converts to SYMNAMES -- It doesnot take care of the occurs clauses.
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10886
Location: italy

PostPosted: Wed Jan 25, 2012 7:18 pm
Reply with quote

stop whining icon_evil.gif
looks like You are not reading the answers You get or the manuals
does db2 support the OCCURS clause for a DDL ???

we already told You that OCCURS and redefines are at least murky

Quote:
That could be a starting point.

You are expected to do a bit of work Yourself
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Wed Jan 25, 2012 8:52 pm
Reply with quote

I know DB2 does not support occurs and Redefines clause...

When Ever a redefines is there... We need to create a new column
and for the occurs clause we will create that many columns in the table...

I know there is a limit in number of columns in a table - 750. But still we want to create the DDL with more columns and later we can manually handle such scenarios.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Jan 25, 2012 9:17 pm
Reply with quote

Quote:
When Ever a redefines is there... We need to create a new column


which means you should be defining these as NULLable.
but that creates real problems when your staff is not experienced in db2.

these redefined fields should generate yet another table or two or three,
depending on the number of redefines.


Quote:
and for the occurs clause we will create that many columns in the table.


that is why i consider the direct vsam to db2 conversion as dumb.

the columns created due to the occurs should be a separate table.

at least you are not making it one varchar column with the repeated values,
magically imbedded / though that would take less space.


though your conversion may be easier,
the the table structure that you create when you do this direct conversion
forces your sql to be complicated.

so you have to make a decision,
quick conversion and 10 years to debug sql
or
couple months invested in proper table design to allow your inexperienced staff to write simple sql.

Quote:
I know DB2 does not support occurs and Redefines clause...


that is why you need to do some proper table design,
db2 handles these concepts (redefines and occurs)
thru the table normalization process.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts VSAM LISTCAT INFORMATION JCL & VSAM 2
No new posts Access to non cataloged VSAM file JCL & VSAM 18
No new posts Merge two VSAM KSDS files into third ... JCL & VSAM 6
No new posts CVDA value for RRDS VSAM dataset. CICS 2
No new posts VSAM return code 23 - for a Random read COBOL Programming 4
Search our Forums:

Back to Top