Plex6.1 DB2 Using a VARCHAR to save diskspace

[renierne]

Hi,

We are using plex 6.1 with db2, we have a table with a field longdescription VaryCharacter 1024 for some reason when I look at the table the data gets stored as 1024 even if the data you insert is 7 long?

Is there a way in plex that we can store a varchar? instead of the full length 1024 to save diskspace?

[Yorkshireman]

Hi

 

What are you using to view the data?

 

in most places, the IBM I and DB2 'helpfully' give you a view with the max length of field visible, and not the data length 'hidden' part,

 

I've had to explain all this a couple of times to new young programmers in debug who suddenly see the length bytes made visible.

 

 

- just shows how an assembler upbringing still has relevance..

 

 

R

[renierne]

What I did was the following:

 

I generated a table using plex the table has a field "Description" thats VaryCharacter and length is 1024.

 

Then I created a function to insert 10 000 rows into the table.

 

Using IBM Data studio and exploring the table and generate DDL of the table it says that the field "Description" is a CHAR 1024.

 

In the Green screen I WRKOBJPDM and view the table description to get the size in bytes of the table, my example the table size were +- 20mb.

 

Now I go back to the IBM datastudio and alter the table and change the field "Description" is a CHAR 1024 to "Description" is a VARCHAR 1024 ALLOCATE(8) and by doing this I expect that if i insert a row by using plex thats for example 7 characters long to insert it as 7 long not 1024.

In SQL when I get the length of "Description" it returns 1024.

Next I run a SQL statement to update updated the field "Description" with the trim(Description) .

If I run the same SQL again to get  the length of "Description" it returns 7. and that I expect it because the data is only 7 characters long.

 

The problem that we have is this table is huge and its because if the 1024 field thats not a varchar or plex doesnt generate the table as varchar. and always inserts 1024 instead of the size of the data.

 

Is it possible in plex 6.1 to create a varchar on a table and when inserting example 7 characters to store only 7 characters and not 1024? this should decrease used space.

 

Hope I make sense ...

[Anon Anon]

As of my knowledge there is no varchar support in the Plex DDS/RPG generators. You can use varchars with  SQL/JAVA and SQL/.Net generators only.

 

Best

Lorenz

[renierne]

Thanks for the info

[Anon Anon]

Hi,

 

We have modified Plex generated DDS-source before building adding VARLEN lines and using RPGIV when generating database functions.

 

     A            CP4IRA              TEXT( 'MyVarLenField )

     A                                      COLHDG( 'MyVarLenField' )

     A                                      VARLEN

 

Regards,

 

Teemu

[renierne]

Hi thanks great idea,

 

I am trying to implement this.

 

I changed the Physical table DDS source by adding the VARLEN and when I generate the field is now a VARCHAR but... could you please help me with the insertRow function... I changed the UpdateView DDS also with the VARLEN  and it builds fine but when I try to generate the InsertRow it fails.. Do you use the normal InsertRow RPGIV function or did you create pure RPGIV function(NON Plex)?  In my spool file the following error :

Data attribute of externally described field is not the same as a prior definition.                                    

Varying length input field is not defined internally as   

varying length.      

 

What am I doing wrong?                                    

[Anon Anon]

Sorry, I forgot that InsertRow function needs some source code additions.

 

In Pre Point Description we have source code TrimVarylenghtField1 for adding helper fields:

 

     D Length          S              5  0 INZ

     D Field                     12000A   VARYING

 

In Pre Point Start insert row we have source code TrimVarylenghtField2 for trimming two VARLEN fields:

 

      /free                                           

                    eval      Field= %trim(&(1:));

                              Length = %len(Field);

                              %len(&(3:)) = Length;

                    eval      Field = %trim(&(2:));

                              Length = %len(Field);

                              %len(&(4:)) = Length;

      /end-free                                       

 

Parameters 1 and 2 are mapped from InsertData and 3 and 4 from View variable.

 

-Teemu

[renierne]

Thanks Alot Teemu, It worked!! this will work until Ca fixes the issue.

[George Jeffcock]

There are so many issues trying to make this work with out CA enhancement. You basically need a transformation layer for all the INSERT, UPDATE, FETCH functions as any CA PLEX generated calling function will expect be generated fro 1024...

 

What happens when your UI Tier wants to get the data..is it a plex tier if so it will expect 1024... so in your server fetch function you will need fetch using SQL and then move the found data into your output view which contains the 1024 field.

 

I have had to do this for The Four Hundred--Remain Software ALM Tool Gets CA Plex Interface as understandably Remain use modern DDL descriptions.

 

Download stellatools and see StellaTools.Remain.Data.Implemented.SQL_LongNameVaryingLength

[renierne]

Yes that make sense..

 

We are using Plex Tier in some instances and SqlBlockfetches the Sqlblockfetches I think will work but not the Plex Tier normal functions...

 

The correct way will be if CA fixes it..

 

The Idea is now 9th on the list RPGIV - Support Date and varchar data types in RPGIV generator from 20 this morning...

[George Jeffcock]

RPGIV - Support Date and varchar data types in RPGIV generator

 

it was penciled in for plex 7.1 but moved to plex7.2 and finally dropped due solely to customer disinterest, get voting and you might make a difference.

[renierne]

Thank you, will make my whole team vote if it will help.. , we will also be looking at to create new patern to use SQL to create our tables and create functions that will insert the data using SQL code.

In the tests we did, when we manually created the tables and inserted it using SQL  it worked... so if we can create a SQL patern this should work.. at least we can implement this solution then for some of our big tables until CA Fixes it or include it in a release...

[George Jeffcock]

https://communities.ca.com/message/104217023

 

https://communities.ca.com/message/241720490#241720490