IBM i DDS --> SQL

[Anon Anon]

Hi all, 

has anyone managed to convert physical tables from DDS to DDL (automated)  and still run Plex functions against the DB ?  And if so, have you been able to change the Plex "create object" process to do the conversion ? 

Best 

Lorenz 

[Kiyoshi]

Lorenz,

This was a main topic for my team for hackathon in the last user conference in NY.

Plex has a capability for a long time indeed.

Step back a little,

DDL or DDS is just a language to create DB object. All you want are object as a result of compile or whatever process.

Plex has a capability to build Table via SQL using ODBC driver.

IBM provides DB400 ODBC driver that can point to library on IBM i.

So answer to create SQL Table on IBM i is that generates Table source via ODBC generator and let it build via DB400 ODBC Driver.

Then you get the SQL Table on your library in IBM i.

 

HTH,

Kiyoshi 

[Anon Anon]

Kiyoshi -

Yes, I remember the Hackaton! 

This solution will probably work in simple scenarios. As soon as we are confronted with large legacy models, we will probably run into problems. 

Let's take a field defined as  AS400 format Binary : 

ODBC generated table: 

AAZA       ZONED        4  0       4       112        Both     AAZA  

DDS generated table:

AAZA       BINARY       4  0       2       112        Both     Binary   

 

Also we would like to have long names on the tables and use alias for RPG access via DDS LF. 

Still think, there should be full SQL Support form IBM i  :-)

Best

Lorenz 

[George Jeffcock]

Well hello I this is the topic as you remember I asked and lead at the hackathon.

Am looking at ways but a bit of background:

DDS variant from plex creates

PF-DATA (Physical File) and LF (Logical File with No Index)

But I would suggest we want to get to

SQL Table and LF (Logical File with Index).

We need a 3 step approach:

• Create/Update PF to SQL Table. (Hackathon method, and you could look at DBMS scripts as a work around for column headings)

DBMS Scripts (SQL Only)

You can incorporate hand-written SQL source code into a generated view in the form of DBMS scripts. The source code is stored in a CA Plex object of type source code and referenced by the VW DBMS script SRC verb.

• Create Indexes for the keys of your LF
• Create LF again but this time it will pick up the index and use it their harness SQL world..page sizes for dds is 8k and now will be 64k.

That is what stage i am at!

Need to investgate INDEX support for as400 variant in plex and maybe adapting 'Create object' for LF to chk for index as if the index does not exist the LF is created with out INDEX and the SQL world is lost to this object.

This way of creating a TABLE not PF and creating DDS Logical files but with INDEX then your exsiitng and NEW plex native reading functions WORK but at the hackathon we could not get the exisitng functions to work over new built LF via DDL.

More latter

 

 

 

 

 

 

 

 

 

 

 

 

[Deactivated User]

Hi all,

This subject is quite interesting. At the Chicago conference I presented on how to do this in 2E.

My approach was to replace the PF/LF objects using the QSQGNDDL API, using the 2E Post Generation processing to invoke code to do this. My 2E models generate DDL, and we continue to use Record Level Access for almost all of our code.

George, why do you need an LF and INDEX?
Also, why is the SQL world lost to an LF object?

The presentation is available in the downloads section of the forum. It might not be as nice as generating directly from the model, but it should work. I also know that

Given that Plex currently generates DDL for other DB implementations, I think it would be relatively easy for Plex to allow a DDL implementation for the IBM i. They may have to tweak for i specific code, such as RCDFMT.

I also had a good coversation with Niels from Websydian who had done something similar with Plex.

I've been brief, have a fair bit of experience in this area...

Crispin.

[Anon Anon]

Hi Crispin - 

thanks so much for your comments. We do not have pre-/post processing of generated code in Plex, but we could change the  YKTCRTOBJC program to achieve what  we want. Your presentation mentions two sources on how to use the QSQGENDLL api. Do these examples work without tweaking, or did you have to make changes ? Could you provide the source code ?

May be Niels has done this already. 

Best regards 

Lorenz

 

[Deactivated User]

Lorenz,

If you search for GENDDL using google, the very first hit is an IT Jungle article with source code. I am not sure if it is the one I used. Any of the implementations should work, the API is the same API used by operations navigator when you right click and generate DDL for an existing database. No tweaking is required.

Changing YKTCRTOBJC would be the way to go, I would think.

In my presentation, I think I provided source code that I used to implement the QSQGNDDL API in a 2E EXCEXTFUN. You could just as easily take that source code, and plop it into a Plex ServerExternal function.

Crispin.

[George Jeffcock]

MyEntity has one key one has field

MyEntity is EDITDETAIL

Step1 GEN and BUILD  -  Physical table for ODBC

- SQL Table is created instead of PF

Step2  Add triple Index Yes to both Fetch and Update, Add triple Index Name MyEntitySQLKeys

Step3 GEN and BUILD either fetch or update for odbc and the SQL VIEW and the SQL index will be built

Step4 Delete SQL VIEW just created because you want your exisitng RPG functions to work (existing will work) and to be able to rebuild them (wont build againt SQL View, where we got to in the hack) and add new functions.

Step5. GEN and BUILD Fetch and Update views as DDS (magic they pick up the INDEX!)

Step6. Optional GEN and BUILD functions if not already exisiting.

 

And there you have it. A SQL Table with DDS Views (using SQL INDEX) all created out of CA PLEX. 

 

I liked this:

http://www.quser.org/Docs/Db2_Indexing_Tips_Tricks_MIPS.pdf

 

 

 

[George Jeffcock]

Interesting http://stackoverflow.com/questions/7045254/as-400-db2-logical-file-vs-table-index

‘improvements to the SQE optimizer have virtually eliminated situations where Logical Files would cause DB2 revert to using the CQE optimizer.’ 

‘In more recent releases of the IBM i operating system, the page size of a logical file can be specifie

DDS not strategic for IBM but the two points above if validated removes some what of the reasons to look at SQL views

 

 

[Deactivated User]

George,

Both of those points are valid ones, on more recent releases. It's not really about INDEX vs. LF though (in fact, I am not really sure there is any difference nowadays). It's really more about DDS vs. DDL.

Enhancements to the database layer on the IBM i are not being pushed into the DDS language, so you can't describe the new features in DDS. Using DDL, you can.

One of the most important differences between a DDS described PF and a DDL described TABLE is when the data is validated. DDS is validated on READ and TABLE is validated on WRITE. This means you cannot put invalid data in a TABLE (for example character data in a numeric field), but you can do that to a PF.

And SQL INDEX, using the RCDFMT clause, can describe exactly the same implementation object as a DDS LF (except for Join LF's, multiple format LF's) down to the Record Format Level Identifier.

Crispin.

[Anon Anon]

Crispin:

 

Enhancements to the database layer on the IBM i are not being pushed into the DDS language, so you can't describe the new features in DDS. Using DDL, you can.

.

This is an interesting statement. If we use the QSQGENDLL approach, we will be locked in the future. 

Best

Lorenz 

[Deactivated User]

Lorenz,

Yes, absolutely. QSQGNDDL takes the DDS and generates the DDL required to replace the DDS object. So you're never going to get any of the improvements in the database that aren't pushed into the DDS language.

The QSQGNDDL approach, in my case, was to take advantage of the Long field names for external tools such as DB2 Web query. Users don't like implementation name...

As I said at the start, the only real solution is for Plex to support DDL generation for the IBM i (I mean proper support, not via ODBC). But QSQGNDDL will get you to long names. It does require some parsing to add the FOR COLUMN clause, but I was able to manage that, so it's not rocket science :)

Crispin.

[Andreas Kiss]

Hi George,

one of the problems is that you have to built all your INDEX and LF's in the RIGHT/CORRECT order to gain all the benefits!

Sharing an SQL index is different than sharing keys from a LF.

--

Andreas

 

[Anon Anon]

George -

Thanks a lot. I think the view created does not have the correct record format and therefore the RPG program fails. If we delete the view and create an LF the record format is correct. 

In a pure SQL implementation where we use meta to generate the necessary SQL statements there is no need for views at all.

There are still problems when the DB2 tables contain numbers in binary format.

Best 

Lorenz 

[George Jeffcock]

Hi All,

As we know CA have not provided this support and all we can discuss are work arounds.

I championed this idea at the hack to try and get some movement on this lack of support.

But a workaround purely out of the box from Plex IDE is promising

Let me know how you get on.

 

[George Jeffcock]

plexcat:

There are still problems when the DB2 tables contain numbers in binary format.

And Price (15,2) Packed is created as FLOAT for ODBC which then prevent pgm being built

And VaryCharacter is created as VCHAR for ODBC which then prevented PGM being built

Had to hand edit ODBC source and convert FLOAT to %numeric%(15, 2),
Had to hand edit ODBC source and convert VCHAR to %char%()1024,

 

and then all worked.... so in my opinion ODBC is a bust, hand editing source unfortunately renders this experiment over.

Will continue the wait for true support.
 

[Anon Anon]

George -

many thanks for testing numerics, varying etc. I expected type problems and just checked the binary stuff. 

It seems that using QSQGENDLL is the only viable option for the time being. 

Best 

Lorenz 

[Kiyoshi]

Lorenz,

 

Here are 4 triples could help you.

FLD DBMS type NME --> You can give a native type name such as "Binary"

FLD AS400 format SYS --> define data type for IBM i world

FLD CFormat SYS --> define PC side program data type

FLD SQLFormat SYS --> define SQL data type

For varchar..

FLD AS400 format SYS Char

FLD CFormat SYS Varring

FLD SQL format SYS Char

 

For binary

FLD DBMS type NME Binary

FLD AS400 format SYS Binary

FLD CFormat SYS Integer

Don't have a test env so don't know the outcome but these triples gives you some flexibility against the field.

Plex has a 'default' data type concept off course. such if you only define Fld Type SYS Character, all platform specific type get default to work the best.

But you can define data type per target platform.

HTH

Kiyoshi

 

 

[George Jeffcock]

Sorry Kiyoshi these triples dont help.

You would have to change the triples each time you gen and build.

You choose to build a table, you either change STORAGE to be ODBC or add langauage triple ODBC or have some pattern/Varaint switch but the end result is you generate the source of the table as DDL instead of DDS, it takes the field definitions from the model and in my case as400 format triples are all present but still decided what was reprented in the model as a FLOAT instead of 15,2 packed number.

 

This sort of example will only be the tip of the ice berg, the work around to this work around... would be when you are gen and build the source for ODBC you rememeber which fields are 'interpreted' 'represented' not the way you would like and hand edit the code or go a step back and change the field inheritience so that when generated for ODBC the source in this case would not be 'FLOAT' but 'Numeric 15,2' Then gen and build, then change triples back so that your programs can be built with the as400 format triples... mess

True support required and was keen to show ODBC could work but am now of the opinion it is a bust. Roll on native support

 

.....

 

Thinking in the car home thought you might get away having these triples permanately but for example what will occur when the winc clients are rebuilt..need to visit many fields and add triples that only have meaning for the server but will affect the client potentially

 

[Kiyoshi]

George,

I agree for TBL/VW language SYS part is mess, but you saying adding/.changing triples per language of Function tells me you are not on the same page with me about those FLD level riples.

What I suggest is defines those 4 triples base layer and use without changing.

For binary case, i.e.

FLD DBMS type NME Binary

This will let plex generates column definition in ODBC TBL source as "Binary". It is your responsibility to set correct Name. 

In the other word, this is the way to use vendar specififc data type which Plex doesn't support.

IF you want to share the model with other ODBC taraget, yes, you maybe better to have variant on your model. 

FLD AS400 format SYS Binary

This tells Plex RPG and DDS generator that field type is Binary.

FLD CFormat SYS Integer

This tells Plex C++, Java and .NET generator that field type is Integer.

With above one time definition and switching the language for the object. (I have length 4 triple as well..)

Client side codes has an information for both C format SYS in RED and AS400 format SYS in Green.

You can imagine how this information will be used when exchanging data between Client and IBM i dispatcher.

C++ code for the field

OB_RTDI_FLDD( 18   , 0xFFFFFDE9, 'n', 'i', 'b', 'n', 4    , 0    , 2    , 0    , "Bin", Bin_18);

C# code for field definition

new ObIntFld(ObField.VIEW_KEY, 4, 0, 'b', "AA3jA", "", M_ObApp, ""));

RPGIV D spec

D  F00001                        4B 0

ODBC source for TBL (IBM help says that Binary can have no parm or int like "Binary (4)", you can give a FLD DBMS type name as "Binary (4)" indeed. But you cannot do like "Binary (4, 0)" because ',' is invalid char for Plex object ... )

AA3jA                             Binary

Or...

AA3jA                             Binary (4)

using these 4 triples, Those custum Types should be Logically exchangable, which includes between TBL and RPG PGM, Client PGM and ODBC layer.

Plex online help for FLD DBMS type NAME documents using GUID as an example.

It is data type 'uniqueidentifier' In MS SQL Server but it is just length 36 String. So Plex side it is defined as length 36 String. So data exchange will work and user get some additional feature via type "uniqueidentifier", which may be generate NEW GUID as a part of Insert, i.e.

Anyway, there is a chance IF you find the Logical exchangable data type for SQL Table AND RPG and C++/C# for certain data type but not all you want.

Especially between, RPG and TBL, which has no chance you or we 'as runtime' to interrupt there. 

This is what you can squize from plex at this point. And I think others are aware of the fact. Also proper SQL support for IBM i doesn't come up tomorrow.

So we are discussing in the forum. And this hot threads hopefully get other users and CA management attention.

Again, I don't have an environment to try but Don't stop because Gearge of comments!

Regards,

kiyoshi

 

[Anon Anon]

Kiyoshi -

I played with the binary type. A Binary (4,0) corresponds to a SMALLINT.   FLD SQL format Small_int   works. 

If we want have a SQL decimal type, we are out of luck: 

I can use     FLD   DBMS type NME  Decimal      but not     DBMS type NME Decimal(11,2) 

When I use a DBMS script on the field, there is the old problem with the comma after the last column that causes the ODBC driver to fail. 

Content of Script :  (11,2), 

CREATE TABLE AAkT (

    AAuA                              %char%(10)

#OPTION CK

                                      NOT NULL

#END_OPTION

    ,

 

    AAvA                              %char%(50),

 

    AAwA                              %char%(1),

 

    AAxA                              %char%(50),

 

    AAzA                              %smallint%,

 

    AA10A                             Decimal(11,2),

 

    AA11A                             Decimal(11,2),  <<<<< comma problem <<<<<<<<

 

Best 

Lorenz 

 

[Kiyoshi]

 

If we want have a SQL decimal type, we are out of luck: 

I can use     FLD   DBMS type NME  Decimal      but not     DBMS type NME Decimal(11,2) 

When I use a DBMS script on the field, there is the old problem with the comma after the last column that causes the ODBC driver to fail. 

Content of Script :  (11,2), 

 

Lorenz,

The solution is obvious for decimal case? I assume you remove the extra comma manually, it success.

Open Support issue for to deal the last comma problem for ODBC generate OR build process? But it still ugly since the script should have length outside of triple.

Have you tried SQL Fromat SYS Money? Will see how ODBC driver convert the type. The same time Money data type is kind of odd one these days. The ODBC build process uses Driver Vender infor or other meta data at a time of build. Like we have some special logic, to get DataType Varchar2 for Oracle or other logic to deal SQL ANYWHERE. So there could be a chance that we can decide SQL Format SY Money for DB2 driver, let's built as decimal type.

If any of these idea will help to move forward, that is way cheaper and easier than generating new type of source code from Plex? 

Best,

Kiyoshi

 

[Anon Anon]

Kiyoshi -

Let's sum up what we have. There are two ways to get to DDL described DB2 database from Plex that are not too expensive for CA. 

a) Change the build process for tables and views. Pass a parameter like  "ConvertDDSToDDL"  true/false from the Gen and Build. 

Take compiled DDS (in a temp library) run QSQGENDDL to get the DDL source and then create the tables, views and indexes. Change the build process so that there are hooks where users can add additional processing to the generated DDL Source before submitting the CREATE TABLE, CREATE INDEX, CREATE VIEW statments. This is necessary to add long names to the source (meaningful names for users of BI products) and probably do other things to the source. 

This provides a cost effective solution using DDL views that work with the Plex generated RPG programs. The only drawback: We are limited to the functionality of DDS in terms of what can be described. 

b) Use the ODBC Generator to create tables. Manually fix code.  Manually remove the unusable views (no RECFMT) from the database. Switch between a pseudo ODBC variant and an IBM RPG server variant. Generate and compile a LF (logical file) that works with the generated RPG. 

I still prefer a) and I think we should have a look at it. 

Best regards 

Lorenz 

[Kiyoshi]

Lorenz,

Option a) is never be my pick.

I believe such commands are mened to use one time for manual environment such without Plex generator.

As you stated at the end, "We are limited to the functionality of DDS in terms of what can be described." this will come bottle nck later.

Example, Plex generates both RPGIII and RPGIV source natively without any command help and compile. (I remember that there is RPGIII to RPGIV converion command.)

Not to use the conversion command gives Plex to expand functionality for RPGIV only feature such Unicode, longer field length, free format etc without fear of 'RPGIII' limitations.

Now there is one option you guys can work toward what you want.

I may be "Speak of the Devil" It is not supported BY CA.
In Plex IBM i library, you find MSGF YKTRCMSG. If you look message YKT0002 and YKT0003 you will find familiar commands with parameters that you can guess where they are come from.

You can guess base on this info, how Plex IBM i build process deals specific Object type.

Some users might already be adding additional parametesr against the message YKT0002 to customize PF-DTA creation, setting specific CCSID, record size etc..

Well, do you think you can swap whole command line by your custom made command? As far as if you use the same parameters and returns the same result?

Just an idea.

I didn't tested this and this isn't the way to go IF we decide to support SQL for IBM i.

It is just one of the option/possibility at this point to achieve the result.

Kiyoshi

 

 

[Anon Anon]

Kiyoshi -

Yes, I we can modify the YKTCRTOBJC or the message file. I will give it a try. I am sure there will be some limitations. Crispin mentioned problems with select/omit. 

I perfectly understand, that you don't want to implement a) as a solution to the problem. On the other hand you probably understand, that we don't want to edit source code manually and delete unnecessary or useless views etc. Also we don't know if ODBC will be a future bottleneck :-) 

I am sure that we will have limitations regardless of whether we go for a) or b).

From a customers perspective I accept that a) will be at our own risk. 

Is there any comittment for b) at CA ? I don't want to spend too much time on finding all the problems only to find out that they won't be solved due to time/cost constraints. 

and at last   -   c) ! 

c)  Allow Plex to generate correct DDL , transfer it to the server, and run it against DB 2 .    TBL  language DDL , VIW language DDL  if that fits into the strategy ....

Best

Lorenz . 

 

[George Jeffcock]

Cavet: When CA Plex supports Table language DDL forAS400, and VW for DDL for AS400 and the big one the trivial functions (CRUD) would have to access via sql to allow the W for DDL for AS400 will be a good day and imo the natural way CA Plex should support this tech requirment

But in the meantime I don't blame CA for not supporting this tech requirment as I believe their customers should had have been asking for this for years. Why haven't they (and me) has to be left for another discussion.

So I beleive once the genie is out of the bottle (for me it was when I watched Crispin's presentation while at Chicago) I became educated and since then I think it is criminal for me to settle for creating DDS when I know there is DDL and SQL. So I search for a way to do this.

I prefer a work around soley based on the CA Plex IDE, my personal choice, just feel more in keeping in the natural ethos

Cavet: Refactoring is great and I have to stop myself doing it in the context of patterns all the time but this DDS DDL thing for me is ONLY for New developement (and maybe problem tables due to performance)  untill native support is avalaible.

ODBC does have a new problem thou - what if a plex site already uses the SQL  format type triples and use SQL server for example then if what is generated does not match what the as400 requires in this workaround you are screwed...

 

 

[George Jeffcock]

maybe another issue or non issue:

The SQL table gets created with KEYS... by the ODBC way. Someone more learned may suggest if this is bad or not but I was under the iimpression a SQL table should have no keys.

But at least this enforces at a DBMS level unique records. 

 

 

 

[Anon Anon]

George -

You can change the build setttings for ODBC to not generate primary key constraints. 

Best 

Lorenz 

[Anon Anon]

George -

just tried to remove pk constraints, but they are still generated .... 

This should not be a problem though. PK constrains are useful to ensure key uniqeness. 

Best 

Lorenz 

 

[Kiyoshi]

GeorgeJeffcock:

ODBC does have a new problem thou - what if a plex site already uses the SQL  format type triples and use SQL server for example then if what is generated does not match what the as400 requires in this workaround you are screwed...

George,

This is something not new for IBM i, ODBC could target to multiple vender's DB. The ODBC layer could be varianted with Plex model configration to support different Database.

variant could be ODBC MS SQL Server, ODBC MYSQL, ODBC SQL Anywhere, ODBC DB400 etc..

This is not screwed, it is still managable situation from pure Plex modelling point of view.

Wel I understand, as user it is still feels mess or screwed

regards,

Kiyoshi

 

[George Jeffcock]

 

 

This is not screwed, it is still managable situation from pure Plex modelling point of view.

 

I think this time you have misunderstood me ;) It is screwed for the purpose of the ODBC workaround but if and when you create new 'SQL format AS400 Sys' verb then we are fine.

But as it stands consider a Plex site that has a field called MyPrice and currently is a database field both on a DB2 and SQL Server.

Consider the site has ALREADY used SQL format Sys  MONEY  for MyPrice and it has worked for their SQL server implmentation for years now you cant just remove this triple because it generates the wrong code for as400 sql implmentation. Now we could control this by variant but i think SQL format AS400 Sys might be easier.

 

 

 

[Anon Anon]

George -

If we are targeting multiple databases in the same model / application the concept of variant does not apply. The Field domains should be structured using inheritance : 

Domain_Price                     is    FLD Number 

Domain_Price_DB2            is    FLD  Domain_Price 

                                           sql type   Decimal

Domain_Price_SQLSrv       is    FLD  Domain_Price

                                            sql type  Money 

 

I am using the concept of "variant" if we target multiple platforms e.g. generating the same application for .NET and for Java. (ISV model). 

[George Jeffcock]

Understood and known.

My point is green field sites with someone like you will make this decison (using the tool correctly) but 9 times out ten only one field will have been used at the Plex site that has had a varied history with the CA Plex tool.

 

[Simon_Cockayne]

Hey there Lorenz,

Candidly, I think CA (and especially me and Dan Short) are committed to try to help the CA Plex customer base be successul, as you say, with the time and resources we have.

A factor that weighs heavily in prioritizing what goes into future releases, is the from the voting and collaboration on the Idea wall. 

I won't commit to future features, because I won't make a promise I cannot keep.

However, like all past releases, the more folks that express a need for a certain capability from the product, the more likely we are to consider it.

Context
You asked "Is there any comittment for b)* at CA ? I don't want to spend too much time on finding all the problems only to find out that they won't be solved due to time/cost constraints."

* "b) Use the ODBC Generator to create tables. Manually fix code.  Manually remove the unusable views (no RECFMT) from the database. Switch between a pseudo ODBC variant and an IBM RPG server variant. Generate and compile a LF (logical file) that works with the generated RPG. "

Cheers,

Simon Cockayne

Sr Principal Product Manager. Product Owner: CA Plex / CA 2E.
https://twitter.com/simoncockayne
CA Technologies | 2291 Wood Oak Dr. | Herndon, VA 20171-2823
Office: [phone] 

[Anon Anon]

Hi Simon - 

We are looking to enhance support for the IBM i DB2 platform and make use of state of the art technologies. This involves two steps: 

1) Need to generate DDL for DB2   (existing RPG IV generator can coexist ) 

2) Support SQL access to the DB in the RPG IV Generator (support for execute SQL etc.) as we have for .NET and Java. 

Arguments: 

IBM will no longer add features to DDS.

Need meaningful long names on DB for users of BI products. 

Performance improvement. 80 % of all DB operations are reads. DDL described tables will validate at insert/update, DDS PF's validates at read. 

Integration of existing databases already defined in DDL.

Get rid of source code used to implement SQL fetch , mimimizing the maintenance work when generating to different platforms. 

Difficult to sell a product that does not support DDL and SQL access. 

etc. 

Even if we come to the conclusion, that RPG is old fashionned and we should move to Java on the IBM i, there is a need to generate the proper datatypes for DB2 such as DECIMAL, NCHAR etc. 

You are right, that these requirement should be posted on the idea wall.

I think the need for SQL access is a very old requirement, that was "solved" by adding language triples to start the correct compiler when using SQL source code. The source code has been written and is now present in most models I come across nowadays. So probably there isn't much interest in supporting Exec sql in the RPG variants today. 

DDL generation is a different animal and I hope there will be some interest in this. 

Best regards

Lorenz 

[Deactivated User]

plexcat:

Even if we come to the conclusion, that RPG is old fashionned and we should move to Java on the IBM i, there is a need to generate the proper datatypes for DB2 such as DECIMAL, NCHAR etc. 

Lorenz,

That would, in my opinion, be the wrong direction to go (Java). RPG is going to be far superior at doing business rules and database access on the IBM i.

Plex has 95% (Crispin approximation, not published fact) of what is needed in it already with the existing DDL generators. They just need to make it work for IBM i generation (adding RCDFMT keywords for example).

If this gets done, then implementing data access should be controlled granularly, with the ability to generate Record Level Access (RLA) and SQL Data Access in the implemented RPG code.

Just my 2 cents...

Crispin.

[George Jeffcock]

My finding today with an enitity pattern

Physical file will always be set to ODBC, no switiching required

Physical File is keyed by PK and therefore no index needed for default Fetch or Update (DDS)

Adding a new VIEW with new keys involved creating a new view and a new dummy view and index. I decided I  wanted the index itself to be an object so created a dummy view called IndexbyNewKeys. You could actually just add my new view has index name triple too but this left little visiblity in the object browser that a developer has consiousely created a new index.

Gen and build the dummy view and the index is created and you delete the dummy view, then gen and  build your new view to pick up the new index.

Seems to work and created a new variant to be able to switch off the ability to G&B the ODBC index.

 

oh and also the foreign keys contstraint is the one to watch out for as all the tables you are refering to at least intially are not sql tables

 

[Kiyoshi]

Crispin and all,

Some technical questions that I have as a forum participant/engineer.

Also I want to get more clear picture around this SQL on IBM i.

We should divide this to two topics.

1. The DB Object Creation
2. How to access the DB Object

1) DB Object Creation

First of all, the term DDL "Data Definition Language".

I just realized we may be using the term DDL for two meaning mixed up?

①IBM i source code type name you put to QDDLSRC (This is obvious one without doubt)

②Pure "Data Definition Language" manner (This is platform independent term? Plex generated ODBC source could be this also if Plex generates DDL source for IBM i, it is also fit here.)

The goal for this one is to create SQL type Table, Index, View etc on IBM i library. In my understanding, the ①DDL is an language to achieve it for those users who uses source code editor on IBM i, such the same way with writing DDS or RPG code and do the compile.

The same time, there are native natural SQl support, which could be via common SQL via ODBC or some IBM DB2 native manner.　This is also DDL but more in terms of ②DDL.

My idea to create DB object using ODBC is a kind of based on this ②DDL. The goal is to create DB Object.

My question for this topic is, Is there true/mandatory benefit or requirement has to be via ①DDL to create DB object in IBM i library?

2) How to use DB

 This is very flexible topic even within Plex world (I am not saying all works 'without some level of CA's implementations' or without issue but just in theory...)

It is good that there are so many choices here.

• Access Table/View via SQL syntax from SQLRPGIV function
  • Could be accessible via Plex Dispatcher for other Clients
  • Could be accessible via IBM i local, like 5250 client
• Access Table by creating traditional LF over the Table from normal RPGIV program
• Access Table/View via SQL syntax from Java on IBM i via JDBC
• Access Table/View via SQL syntax from Java on non-IBM i via JDBC
• Access Table/View via SQL syntax from .NET function via ADO.NET provider for DB2
• Access Table/View via SQL syntax from C++ function via ODBC driver for DB2
• All options have a potential to expose those DB via passing buisines logic via non Plex world with WCF, EJB, Web Service or COM

I assume those regular PF-DTA and LF work with those common DB access methods like ADO.NET, JDBC or ODBC. However it is not designed to be work natively I assume there could be some known issues like performance, data type etc.

 

Regards,

Kiyoshi

[Anon Anon]

Kiyoshi - 

DB objects for DB2 are created using  SQL DDL (Data Definition Language) and by submitting the CreateXXXXX instructions to the DB engine. This can be done via ODBC, JDBC or issuing a RUNSQLSTM (run sql statement) directly on the IBM i. As long as the ODBC and JDBC drivers support the the same set of instructions, the methods are equivalent. 

We have almost everyting we need to create the DB objects for DB2 via ODBC.  Tests show some problems with UNICODE and DECIMAL datatypes. The RCDFMT ist not generated.Also if we need to integrate an existing DDL described DB2 database there might be some issues with types like BLOB, DATALINK, XML, ROWID etc. 

All these issues should be adressed regardless of the programming language that is used to access the DB. There are cases where JAVA is used on IBM i, and there are .NET solutions accessing DB2 databases. 

In order for the current RPG IV generator to be compatible with the tables and views generated via ODBC we must have an exact match of the tables/views generated by the existing DDS generator. 

The question for IBM i shops is: How simple is the change from DDS to DDL ?  Do we still have a generated source on the IBM i ? (compile complete application without the need for Plex being installed (yes, there are requirements like this) ).

Kioshi, you are right about the two flavors of DDL generation for DB 2. 1) Generate DDL using the existing information in the model (types, record format etc) transfer the source to IBM i and run against DB. 2) Add the necessary type support ( FLD sql type SYS) to support DB 2 object creation via ODBC. 

Once we have this functionality, we can talk about  SQL DML (Data Manipulation Language) support for RPG IV. 

Best

Lorenz 

[Deactivated User]

Hi Kiyoshi,

I agree that there are 2 areas to look at here.

1) DB Object Creation.

Re: DDL. I don't see the distinction between DDL stored in QDDLSRC (or more commonly/perhaps traditionally QSQLSRC). It is still DDL, and (other than platform specifics, such as RCDFMT on IBM i) identical. I think it's important to realize this. The mechanism used to compile the code, be it through the SQL engine that ODBC uses or native IBM i commands (RUNSQLSTM) are eventually implemented through the Database layer identically (my assumption being that the ODBC method is directly executing the CREATE statements).

Perhaps that was what you were getting at with your next question re: Is there a true/mandatory requirement for 1. It's a good question.

From an adoption perspective, I think you'll have more luck and it will be more intuitive for those coming from a DDS background to use the same generate and compile process that they are used to. Adding ODBC to the mix may complicate things.

From a change control perspective. Code is generated on a development machine, and then moved to a production machine (and often recompiled). With the ODBC method you have no source on the IBM i.

2) How to access the DB Object.

You covered most of the ways to access the DB Object, but there is one that you are missing, and it's fundamentally the most important one here.

Access Table via Index over the Table from normal RPG/RPGIV Program (Record Level Access - RLA). This one is often hard for anyone who is used to SQL access to a database, because you can't use an Index in an SQL statement, that is left up to the DBMS.

The reason it is important is this. With the addition of the RCDFMT clause in DDL for TABLE/INDEX it is possible to replace existing PF/LF with TABLE/INDEX and create an object with the same Record Format Level Identifier. This means you can replace the database without recompiling (adding risk to the change) any code that accesses the database. Once this is done, it is then possible to add new DDL functionality to your database in smaller moves.

Why would someone want to use RLA? Well, there are times when RLA will outperform SQL. I also think for a shop that wants to take advantage of newer DB2 features that are used to DDS and RLA will find it easier to make moves in smaller steps, and SQLRPG DB Access is quite a step (both in syntax and faith).

To your last question about PF/LF. PF and LF are really implemented in the DBMS identically to TABLE/INDEX/VIEW. There are attributes in the OS layer that differentiate them, but when it comes down to it, the DBMS does not see much difference. It use to be the case that the SQE (SQL Query Engine) would not consider a Sparse Index (LF with Select/Omit) but now that the INDEX DDL supports the WHERE clause this has changed. Many more LF attributes that forced SQL to use the CQE (Classic Query Engine) have been removed in newer releases of IBM i. My belief is this is really because at the end of the day, there is not that much difference between the 2 implementations at the DBMS layer. So, there should be no technical reason that any of the access methods you mentioned would not work.

Some last thoughts.
I am no SQL expert, these are simply a collection of my thoughts on the matter.
DDL on IBM i does not require an SQL COLLECTION. This is often overlooked.

Crispin.

[Kiyoshi]

Hi again,

In sum here are what I learned. (No commitment here )

• To get RCDFMT concept for DB2 world, I feel it is enough reason to deal with DDL source on IBM i source to avoid mess on ODBC side.
• In addition, to get rid of ODBC flexibility about data type setting between ODBC Meta info and Plex model for DB2, just hardcode the concrete Data type based on FLD level triple to DDL source on IBM i maybe friendly way for Table creation and RPGIV generation point of view.
• Since we get rid of uncertaincy between Table (data type) and defined in the Plex model, RPGIV generation could based on conclete information for later SQL generation etc...
• Adjusting ODBC/JDBC/ADO.NET side at runtime to take care of type for DB2 (if there are demands) may be simpler and easier since there is 'Plex runtime' unlike RPGIV world. 

How it sounds?

Best,

Kiyoshi

[Anon Anon]

Hi Kiyoshi -

Sounds good. 

Look at DB 2 reference CRATE TABLE to see what types are currently supported in DB 2. We should be able to choose any of these types. Plex doesn't need to support all types in RPG generators, but we should be able to match existing tables, regardless whether they have been created by Plex or manually (Integration of existing DB 2 Tables with Plex). If types are not supported by the generators, they can be omitted from the views (Example: We should be able to model and create a table that has a DATALINK or XML column). It's a good idea to generate full source (no ODBC, Meta guessing). RCDFMT is one area, meaningful names on columns is another to address. We should think about using MeaningfulName FOR "AA1F" in code generation. Need to investigate a bit further... 

Cheers

Lorenz 

 

Attachment(s)

sql db2 ref.pdf   6.29 MB 1 version

[Anon Anon]

Voting time ! 

https://communities.ca.com/web/ca-plex-ca-2e-global-user-community/ideas

Best 

Lorenz 

[Deactivated User]

Hi Lorenz,

Done. Also added some additional comments..

Crispin.

[Deactivated User]

Hi Kiyoshi,

I agree that avoiding the mess on the ODBC side is a good reason, I did however like my reasons just as much (just kidding )

Not being familiar with all the issues that the ODBC generator brings to the table (forgive the pun) I will leave the remaining questions to your pur-view (sorry, I could not resist another pun)...

Crispin.

[Anon Anon]

Crispin - 

You are right about efficiency of RPG Code. Don't get me wrong I am not advocating a change from RPG to Java for IBM i shops,  but there are scenarios where Java comes into play even on IBM i. 

1) For ISV's it may be better to just support one variant (Java) and run the same code on different platforms. 

2) CM First M3 Process (2E model --> Plex Model --> Web front end) 

 

I also wanted to point out, that regardless of the language that we use to access the DB 2 database on IBM i, we need to have proper dataype support. 

Best 

Lorenz

[George Jeffcock]

could be back to ODBC method as Kiyoshi pointed out (had not read your post previously as we were writing at the same time!)

In my simple example where i had to hand edit the vchar and price when i added the SQL type sys triples it works nicely. They should happily co exist.

Maybe I should look at this extra triples as a step I intially never had to do but becuase I  now building for SQL they are required but should not be seen as a chore.

I have already suggested DBMS and see it as one of our bag of tricks to deal with difficult field definitions and even potentially inserting Column Labels.

I got to the same level as you on the way home in the car re having not to change remove the triples. You are just quicker, sharper, younger than me ;)

 

Now need an as400...this will wait till monday

 

 

[George Jeffcock]

Some Practical Findings after a week:

 

ODBC works but fellow developers who have not been involved in the research and built for ODBC generator before find it hard to adopt.

Decided to use it just for New Development.

- Benefit of SQL tables allow DBMS to control what data is allowed to be inserted but the reverse of this is your exisiting DDS tables may have invalid data and will need to be cleaned before it can be copied to a SQL table version of the file. Some of the functionality may even rely on this invalid data.

- Decided to use DDL where approiate in new development where performance is paramount and not just a standing data table with 10 records

- Because CA Plex will support DDL soon :) was not reason not to do it now using the ODBC generator

- Losing the source is a pain and will involve a manual step of recreating it on the as400 using one of the many tools avaliable

- A departure to how CA Plex has supported INDEX creation before it was felt as a CA Plex developer you would like to explictly create and maintain the index as objects in the plex ide and not simply using the vw name index triple. As the DBA in the Plex IDE it was felt we should know at a glance what INDEX are avaliable for use and maintenace. Maybe VW type Sys Index.

- Keyed SQL tables were acceptable

- Adding Column labels has to be done after creation of the SQL table thererfore Field DBMS script was not possible but Table DBMS scipt would work but how to patternize that...model api...so a bust for the meantime - so will wait for native Ca Plex support of DDL - and yes we can do this outside of plex but ami9 was to achieve everthing in plex

- Be clear to the Plex team this is about database file creation not the access of database files via SQL in CA Plex.

- Promotion of an index can not just be copy object

- Switch off Foreign Constraints if your model relationships point to non SQL Tables