Update Actions - Clear table, then insert rows

[lvw123]

While loading a UDB table from a single input file, using the Update action: clear table then insert rows, I received the following warning: [IBM][CLI Driver][DB2/6000] SQL0513W The SQL statement will modify an entire table or view. SQLSTATE=01504. Is there a way, within Data Stage, to clear the table and insert rows without receiving this warning?

[ogmios]

The way we do it is using a DB2 stage and then using the "Before Sql" to say "Delete from Tablename;". And set it to abort on failure. Then use "Insert rows" as action.

It's only a warning of DB2 but unfortunately the stage picks it up.

Ogmios

[auvray.muriel]

Hy,

I had the same problem.
The problem to make the "delete" in "before sql", it is that if you have many lines to remove, you risks saturated the newspaper with transaction on DB2.
The solution that we installation constitutes to carry out the following order in "before job" :

Code: Select all

    @ECHO OFF
    ECHO +-----------------------------------------------------------------------------+
    ECHO : TRUNCATE DES TABLES DE REJET DANS ENTREPOT DE DONNEES                       :
    ECHO : DATE CREATION : 09:25 05/03/2004  DATE MODIF.: 09:25 05/03/2004             :
    ECHO +-----------------------------------------------------------------------------+

    Rem Parameters values
    Rem %1 : Alias Database
    Rem %2 : user/shema Database
    Rem %3 : password
    Rem %4 : Path of script
    Rem %5 : Table Name
    Rem %6 : Path of log (C:\DB2LOG\)

    IF EXIST %6\%5.LOG DEL %6\%5.LOG
    IF EXIST %4\%5.SQL DEL %4\%5.SQL

    ECHO CONNECT TO %1 USER %2 using %3; >%4\%5.SQL
    ECHO import from nul: of del replace into %3.%5; >>%4\%5.SQL
    ECHO DISCONNECT CURRENT; >>%4\%5.SQL
    ECHO TERMINATE; >>%4\%5.SQL

    DB2CMD.EXE /w DB2CLP.BAT DB2.EXE -tvf %4\%5.SQL -l %6\%5.LOG

    rem DEL %4\%5.SQL

All this code is encapsulated in a command file, and it is this command file which is called in "before job".

Code: Select all

#PathScript#\TruncateV2.cmd #dsn_cible# #usr_cible# #pwd_cible# #PathScript# RJETADBS #PathLog#

Muriel Auvray

[lvw123]

Thanks for the responses.

We also have a delete script that we are calling in the 'before job' section of the Data Stage job. In the past, we were able to do a 'clear table, then insert rows' update action without receiving warnings. I was wondering if anyone had discovered a way to use this update option without receiving warnings and without scripting.

[ray.wurlod]

It always has been and, I believe, always will be, DataStage philosophy to log every item of information received from database servers, even though these are only informational messages.

In the highly litigious USA, this limits the possibility of Ascential being sued because their software failed to make someone aware of some situation.

There are filters for viewing the log file, but all events are logged.

If you'd like to be able to filter out the logging of database events with a status of SQL.SUCCESS.WITH.INFO (this is ODBC terminology, native APIs have their own terminology), generate a product enhancement request, which is properly done through your support provider.

Note that, to have any chance even of its being considered, you will need to provide a business case for your request.

[lvw123]

With the assistance of our DBA and an IBM developer, we were able to set a parameter in DB2 to filter this warning.

Thanks to everyone for their suggestions!

[DaleK]

lvw123 - What parameter did your DBA set and what did you set it too?

Thanks

Version 6 must have ignored this warning. Only started showing up after I upgraded.

thanks
Dale

[mszablew]

I had this problem too. The warning is generated by DB2 because when you set 'Clear then Insert' on your job, Datastage is issuing the command "DELETE FROM TABLENAME" without a WHERE clause, which causes DB2 to generate the warning:-

SQL0513W The SQL statement will modify an entire table or view. SQLSTATE=01504

To suppress just this particular warning, modify your db2cli.ini
and add:-

[common]
IgnoreWarnList="'01504'"

I have tested this and it works.

[ray.wurlod]

Welcome aboard. :D
Thank you for useful information.

[hsbc_ds_dev]

Hello,

Sorry to hijack the thread, but I'm also facing the same problem and the workaround given by mszablew is not working for my project. DB2 (and in turn DS) is still throwing the warnings

Would appreciate if anybody can help me out. Let me know you need more details.

Thanks in advance!

- NJ