Plex 2E

 View Only
Expand all | Collapse all

Plex6.1 DB2 Using a VARCHAR to save diskspace

Jump to Best Answer
  • 1.  Plex6.1 DB2 Using a VARCHAR to save diskspace

    Posted Mar 09, 2015 06:47 AM

    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?



  • 2.  Re: Plex6.1 DB2 Using a VARCHAR to save diskspace

    Posted Mar 09, 2015 07:57 AM

    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



  • 3.  Re: Plex6.1 DB2 Using a VARCHAR to save diskspace

    Posted Mar 09, 2015 08:34 AM

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



  • 4.  Re: Plex6.1 DB2 Using a VARCHAR to save diskspace

    Posted Mar 09, 2015 10:14 AM

    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



  • 5.  Re: Plex6.1 DB2 Using a VARCHAR to save diskspace

    Posted Mar 11, 2015 02:34 AM

    Thanks for the info



  • 6.  Re: Plex6.1 DB2 Using a VARCHAR to save diskspace

    Posted Mar 11, 2015 03:04 AM

    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



  • 7.  Re: Plex6.1 DB2 Using a VARCHAR to save diskspace

    Posted Mar 11, 2015 04:08 AM

    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?                                    



  • 8.  Re: Plex6.1 DB2 Using a VARCHAR to save diskspace
    Best Answer

    Posted Mar 11, 2015 06:42 AM

    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



  • 9.  Re: Plex6.1 DB2 Using a VARCHAR to save diskspace

    Posted Mar 11, 2015 08:37 AM

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



  • 10.  Re: Plex6.1 DB2 Using a VARCHAR to save diskspace

    Posted Mar 11, 2015 04:12 AM

    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



  • 11.  Re: Plex6.1 DB2 Using a VARCHAR to save diskspace

    Posted Mar 11, 2015 04:27 AM

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



  • 12.  Re: Plex6.1 DB2 Using a VARCHAR to save diskspace

    Posted Mar 10, 2015 11:37 AM

    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.



  • 13.  Re: Plex6.1 DB2 Using a VARCHAR to save diskspace

    Posted Mar 11, 2015 02:42 AM

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



  • 14.  Re: Plex6.1 DB2 Using a VARCHAR to save diskspace