Hi,

in our historically grown database (DB2 IBM i V7R1) we have some tables which contain dots in their names.

Ausw. Objekt     Art         Attribut     FA.ART5     *FILE       PF-DTA       FA.ART5_S   *FILE       PF-DTA       FA.ART6     *FILE       LF           FA.ART7     *FILE       PF-DTA       FA.ART9     *FILE       PF-DTA       FA.ART9_CG  *FILE       PF-DTA       FA.ART9_FE  *FILE       PF-DTA       FA.ART9_S   *FILE       PF-DTA 

How to select or update them with SQL ?

This doesn't work:

        replace satz                with #sql{                        update QS36F.FA.ART5                        set                                A5LFM = :A0LFM                                where                                A5AUFU = :A0AUF                 };

Kind Regards!

have you tried

        replace satz                with #sql{                        update QS36F."FA.ART5"                        set                                A5LFM = :A0LFM                                where                                A5AUFU = :A0AUF                 };

Thanks for your reply Bram.

I tried this, but that doesn't work.

EGL0504E REPLACE: Data truncation[sqlstate:22001][sqlcode:-99999] EGL0002I Der Fehler ist in KAPAZ_010_service bei der Verarbeitung der Funktion save_FAART10_A0LFM aufgetreten.

There is a simple work around: use the long name (more then 10 characters!) possibility. 

rename table  QS36F."fa.test" to FA_TEST_LONG_NAME 
rename table  QS36F.FA_TEST_LONG_NAME TO SYSTEM NAME "FA.TEST"             

After that you can use both select statements as beneath:
SELECT * FROM  QS36F."FA.TEST" 
SELECT * FROM  QS36F.FA_TEST_LONG_NAME 

Hi,

thanks for your suggestion.

But thats not possible because there are some logical files on the physical file...

Kind Regards!

An other alternative: CREATE VIEW...

And logicals depends on system_table_name so I still think this is a possible solution:

RENAME TABLE xxxxx/"fa.test" TO FA_TEST_LONG_NAME FOR SYSTEM NAME "fa.test"  

Kind Regards

Jan Willem.                                                     

Ok sorry,

you are right the rename works.

But I've got still the DATA TRUNCATION error.

        replace satz                with #sql{                                update QS36F.FA_ART5_LONG_NAME                                set                                        A5LFM = :A0LFM                                        where                                        A5AUFU = :A0AUF                         };

EGL0504E REPLACE: Data truncation[sqlstate:22001][sqlcode:-99999] EGL0002I Der Fehler ist in KAPAZ_010_service bei der Verarbeitung der Funktion save_FAART10_A0LFM aufgetreten.

I have the same data truncation error on 1 file. It happens very sporadic, and I can't reproduce it.

If you find a solution or cause for it, i'm very interested in hearing about it.

Seems that value of A0LFM has to many characters.....  And keep in mind that some characters take 2 positions. For example ü will use 2 positions for CCSID=37.

Hi,

Thanks again for your reply.

But that's unfortunately not the problem...

Hi,

just one thing I noticed and maybe it is the problem.

In the where clause you have A5AUFU = :AOAUF      Looking at your screen shot, the A5AUFU column s defined as num(2) and the host variable AOAUF is defined as NUM(5).   Did you mean to use the column A5AUF since that is what you had highlighted?  Or is your function argument incorrect?

Mark

Thanks Mark....

thats embarrassing....

Kind Regards!

Believe me... I completely understand...done that type of thing many times.

Glad it works and the help from the others on how to get the table name resolved.