How to force Null into a Blank String Field on a JDE Table?

[sheeba]

When a string field which has no value is inserted into a JDE table -it defaults to a blank . Is there a way we can insert a null instead of the blank via programming(Event Rules) - We have been doing this using sql outside of JDE.

Is this the only way we can do this? We are trying to save space.

 

[Starrjos]

You are trying to save space?

Are you saving your database to a floppy.

How much disk could you buy for the time you spend trying to code a blank out to a null?

What am I missing here?

The answer to your question by the way is yes.

 

[sheeba]

ok - yep ,This is no joke - We are archiving huge Interface Files (a set of 6 to 8 files) and we noticed they use up a lot of space - But When we nullify all the fields that have blanks in them (using sql) it reduces the table space drastically (I am not sure if you are looking for statistics here) believe me its huuuge files!!!

ok - if the answer is yes - how can we achieve this? wud appreciate your help

we tried the ltrim(rtrim) - it defaults the string field to blank if there is no value in the string field .

 

[Starrjos]

Off the top of my head, I would generate a table with 1 column that has a value of null (or find another unused table). Then just read that table record and use that column to set the value of the blank field to a null.

 

[Starrjos]

Forget what I posted previously. I believe if you create a report variable and don't give it any value, the default value is actually null. You can then just map this value to the field you are trying to update from blank to null. You will need to try it and see.

 

[Zoltan_Gyimesi]

Hi Sheeba,

[ QUOTE ]
Is there a way we can insert a null instead of the blank via programming(Event Rules)

[/ QUOTE ]

Though I am not sure but I am afraid, there is no way to do this neither via ER code, nor in C BSFN with standard Table I/O API calls.

All this action go through JDBase middleware and I strongly suppose, this middleware will replace all NULL values with BLANK if they were not replaced previously by the run-time UBE/APP, etc. engine.

I suppose, it is a "heritage" from the "old" AS400/OS400/DB2 "World", where DB2 was/is a fixed field width length database. To solve multi DB support was to use the most sever restriction of supported DBs.

Regards,

Zoltán

 

[sheeba]

yes - you are right Zoltan - I got the same kind of answer from Oracle as well. This is possible only by sql outside of JDE or the like third party tool.

appreciate all your quick responses.

thanks
sheeba

 

[WhippingBoy]

You DO NOT want any null values in your live system. The middle-ware will barf so fast your head will spin.

I'm serious, this is THE biggest problem issue when converting legacy data into new installs.

 

[BOster]

[ QUOTE ]
You DO NOT want any null values in your live system. The middle-ware will barf so fast your head will spin.

I'm serious, this is THE biggest problem issue when converting legacy data into new installs.

[/ QUOTE ]


Yup, there are no true NULLs in JDEBASE. Buy more disks.

 

[Adrian_Chimirel]

As others already aknowledged, it is NOT a good idea to harass E1 into dealing with NULLs. It is not good at it (it will not recognize them consistently). This is the reason JDE replaces NULL with blank - to be able to recognize it.
Sorry, but you need to redesign your tables if they are empty (this is proof they were poorly designed in the first place).

 

[Starrjos]

I am curious Sheeba.....How large is this file you are trying to size down? How large is the field?

I can't imagine that a table with a single blank in a column is really that big.

 

[Larry_Jones]

OK, I'm curious. Are these "files" or database tables? Your terminology calls them both.

 

[Larry_Jones]

Another way to get the same result would be to use Oracle's Database Compression on the tables (preferably on the entire database).

There are some fantastic benefits from database compression whether SQL Server or Oracle. For JDE tables compression ratios ranged from 60-80%. DB Read I-O was 2.7 times faster! The downside is the cost since this is an optional module

 

[Zoltan_Gyimesi]

Hi Joe,

[ QUOTE ]
I can't imagine that a table with a single blank in a column is really that big.

[/ QUOTE ]

I can do

Bespoked tables based on customer's "unmoderatable" demand.

Regards,

Zoltán

 

[sheeba]

appreciate all your responses.

Here's what we are actually trying to do - We are archiving and purging huge Interface tables (JDE Custom Tables)

I wish I could publish the actual statistics here. Our CNC and the Database Admin are trying to find a permanent soln to archiving more efficiently. Here's the most current info I got from them "Current live interface tables are at 1.4 mil records and growing at the rate of 60 – 70 k records a day" - Our CNC truncates the Production Interface Archive tables and saves space. At one point he even set the Blank fileds in one of the files to null, reducing the size of the table from 121 GB to 9.1G using sql.

This has become a real hot issue at our client and we are looking for a permanent fix to archiving these huge interface files in a more optimised way.

Will let you all know what we decide. Also appreciate any tips on this regard.

 

[sheeba]

and again - we are not talking about 1 field in one table - there are like 140 to 150 fields in these tables (6 original and 6 archive ones) and not all of them have values all the time. They are blank most of the time which is actually causing the problem.

 

[JMast]

Sheeba,

The first thought I had when reading this last post is about changing the interface.

If many of the columns are blank most of the time, I would look at breaking those columns off into a "secondary" table. You have the columns that are always (or almost always) populated in your primary interface table. You create a secondary table with a key column(s) from the primary and all the typically blank columns. You design the interface application (JDE or non-JDE) to load the primary table and, when data exists in the columns, the secondary table.

I realize that I do not know what the interface is doing or how it is designed, but I wonder if the effort to do a redesign would be worth it in the long run.

Jer

 

[JMast]

Sorry, a detail I should have added to my previous post.

If you can't change the interface, you could use a workfile process.

1. create a workfile that contains all columns to hold the data coming in or going out.
2. create a job to read the workfile and populate the primary and secondary tables or read the tables and populate the workfile.
3. clear the workfile when complete.