Defining SQL-DECIMAL (packed) fields in Plex

[Legacy User]

Hi all,

we try to "rebuild" DDS defined physical files to create them as SQL Tables from Plex.
Environment is: i5/OS v6r1, CA Plex r6.1
Does someone know, how to define fields as "decimal" (SQL datatype), to get the same fields (and bufferlength) as defined as DDS "packed".
How do I have to define such a field in Plex?

Any suggestion would be appreciated.
Thanks in advance
Axel

[Dean_Eshleman]

AxelOberlaender wrote:

Hi all,
we try to "rebuild" DDS defined physical files to create them as SQL Tables from Plex.
Environment is: i5/OS v6r1, CA Plex r6.1

First, I want to make sure I understand what you are trying to do. Are you trying to get Plex to generate SQL DDL and then build the tables in i5/OS using that DDL? If so, you can't really do that when targeting i5/OS. I have worked around that limitation by generating and building the tables using DDS like normal. Then, I use a GENSQLDDL command to extract the SQL DDL from the file. I then recreate the file with the DDL using the RUNSQLSTM command. The resulting file has the same format level identifier as the DDS defined file.

If this isn't what you are trying to do, please clarify your question.

Dean Eshleman,
Everence Financial

[Legacy User]

deane wrote:

Then, I use a GENSQLDDL command to extract the SQL DDL from the file. I then recreate the file with the DDL using the RUNSQLSTM command. The resulting file has the same format level identifier as the DDS defined file.

If this isn't what you are trying to do, please clarify your question.

Dean,
sorry for my delay, but I was tied up for business the last days.
What you described is exactly what I want to do.
First of all - accessing the file with the functions generated from CA 2E without regenerating them. I know, this is given with what you do.
Second - accessing the database by using JDBC as the method.

And the question now is, what is needed to access these tables, that contain packed fields, via JDBC and SQL from Plex functions?
If we generate DDS-tables in Plex (containing packed fields) with the use of GENSQLDDL and RUNSQLSTM to modify them as you described, can we get access with Plex functions by using JDBC/SQL?
I hope you can imagine what I mean.
Thanks a lot for your suggestions

Axel

[Dean_Eshleman]

If we generate DDS-tables in Plex (containing packed fields) with the use of GENSQLDDL and RUNSQLSTM to modify them as you described, can we get access with Plex functions by using JDBC/SQL?

I haven't done it (the Java part), but I would say the answer is yes. Plex will take care of generating the correct Java code to handle the packed decimal fields. Someone else may want to speak up if they have done something like this.

Dean Eshleman,
Everence Financial

[Legacy User]

I want to try this, Dean. Where did you get that GENSQLDDL-Command you used for?
Is it possible to deliver it to me?

Thanks a lot in advance
Axel

[Dean_Eshleman]

Hi Axel,

Contact me via email, and I will send you the command. My address is Dean dot eshleman at everence.com.

I also wrote a second command called GENSQLDDL2 which is a front end to GENSQLDDL with some additional functionality. Here is the command:

[font=Courier New] Generate SQL DDL For Files (GENSQLDDL2)

Type choices, press Enter.

Database file . . . . . . . . . __________
Database file library . . . . . *LIBL Name, LIBL, CURLIB
Source file . . . . . . . . . . QDDSSRC Name
Library . . . . . . . . . . . *LIBL Name, LIBL, CURLIB
Source member . . . . . . . . . *DBFILE Name, DBFILE, FIRST, *LAST
Database file type . . . . . . . *PF PF, LF
Keep DDS comments? . . . . . . . *YES YES, NO
Add identity column . . . . . . *NO YES, NO
Identity column impl name . . . IMPTRCDNBR Name
Recreate the file? . . . . . . . *YES YES, NO [font]

It does a couple of things.
[list=1]
[*]Copies the comment lines from the DDS if the option is *YES
[*]Adds the IDENTITY column language to the DDL for the specified impl name
[*]Adds long names for each field based on the Column_text field in the SYSCOLUMNS view
[*]Recreates the file using the DDL
[list]

The idea is, you build the physical table from Plex like normal. Then, you run the GENSQLDDL2 command against the file to recreate it using the DDL. Then, you build your Plex logicals. Right now, my GENSQLDDL2 command only works for physical files. There are some tweeks that need to be made for logical files. If you want the GENSQLDDL2 command, let me know.

Dean Eshleman,
Everence Financial

[Legacy User]

Thanks a lot for this, Dean. I will contact you soon.
Axel