ORA-01400: cannot insert NULL into ("SAPSR3"."SDBAD"."SYSID")

[mario_bisonti2]

I applied the note on production system : 1856270 - Performance improvements for tables with single unique index, so SDBAD table has been converted to IOT.

Now, when I execute CheckDB from DB13 I obtain :

BR0280I BRCONNECT time stamp: 2014-08-12 18.00.38

BR0301W SQL error -1400 at location rz20_info_save-11, SQL statement:

'INSERT INTO "SAPSR3"."SDBAD" (BEG, FUNCT, POS, LINE) VALUES ('00000000000011', 'CSL', '0163', '20140812175800 0000000244 0000000000 2147483645 0000 I PSAPSR3         SAPSR3     SGOSHIST~0')'

ORA-01400: cannot insert NULL into ("SAPSR3"."SDBAD"."SYSID")

BR0280I BRCONNECT time stamp: 2014-08-12 18.00.38

Could you help me?

Thanks.

Mario

[former_member188883 · Accepted solution]

Hi Mario,

Please share output from below sql statement

sqlplus <sap_user>/<password>

SET LONG 60

SELECT COLUMN_NAME, DATA_DEFAULT FROM DBA_TAB_COLUMNS WHERE TABLE_NAME = '<table_name>';

Regards,

Deepak Kori

[mario_bisonti2 · Accepted solution]

SQL> SELECT COLUMN_NAME, DATA_DEFAULT FROM DBA_TAB_COLUMNS WHERE TABLE_NAME = 'SDBAD';

COLUMN_NAME

------------------------------

DATA_DEFAULT

------------------------------------------------------------

BEG

FUNCT

SYSID

COLUMN_NAME

------------------------------

DATA_DEFAULT

------------------------------------------------------------

POS

LINE

[mario_bisonti2 · Accepted solution]

Note that the system is a MSCS cluster, but I don't think that it is the problem.

I could to undo the IOT SDBAD table to try if the problem solves with SDBAD as standard table?

I tried this on my test system:

convert to IOT SDBAD and I executed check DB and all ok.

I revert to standard table SDAB and recheck DB and I obtain the same problem as in production system.

But now in test system SDBAD is standard, not IOT

So I don't understand

After conversion to IOT I had a problem with privileges, so I execute sapdba_role.sql to SAPSR3 and that problem missing but this problem stays.

ORA-01400: cannot insert NULL into ("SAPSR3"."SDBAD"."SYSID")

[ACE-SAP · Accepted solution]

Hello

Function code for checkDB is supposed to be 'CHK' and not 'CSL'.

I cannot find on any system an action called CSL.

What is your SAP version ?

The insert command in SDBAD does not provide any value for SYSID field so it does not respect the 'not null' constraint. At first glance it does not seem to be related to the IOT conversion...

Why are you converting SDBAD to IOT ?

You are only suppose to perform that action on table with a critical size, SDBAD should not be that big and should not cause performance problem.

Regards

[mario_bisonti2 · Accepted solution]

Yes, you are right but in production SDBAD was big.

I made conversion to IOT at 150 tables so SDBAD was considered.

My system is ECC 6.0

brtools 7.20 (38)

Kernel 7.21_EXT (300)

Note that I upgraded new kernel 7.21_EXT and brtools too.

I execute the kernel upgrade in my delelopment system SVI too, but I haven't the problem (not conversion to IOT and undo conversion of SDBAD )

If execute "Update statistic" I find the same problem:

BR0301W SQL error -1400 at location stats_dur_save-6, SQL statement:

'INSERT INTO SAP_SDBAD (BEG, FUNCT, POS, LINE) VALUES ('00000000000002', 'DUR', '5293', '00000019TSAPSR3.ARFCSDATA')'

ORA-01400: cannot insert NULL into ("SAPSR3"."SDBAD"."SYSID")

I see:

SQL> desc SAPSR3.SDBAD;

Name                                      Null?    Type

----------------------------------------- -------- ---------------

BEG                                       NOT NULL VARCHAR2(42)

FUNCT                                     NOT NULL VARCHAR2(9)

SYSID                                     NOT NULL VARCHAR2(24)

POS                                       NOT NULL VARCHAR2(12)

LINE                                      NOT NULL VARCHAR2(762)

Could you help me?

Thanks a lot

[mario_bisonti2 · Accepted solution]

The problem, as suggested by our SAP support is:

after conversion of tables to IOT, the column of the table missing DEFAULT  VALUES!

SELECT COLUMN_NAME, DATA_DEFAULT FROM DBA_TAB_COLUMNS WHERE TABLE_NAME = 'SDBAD';

DATA_DEFAULT is empty, no default exists for the column specified in COLUMN_NAME.

A default value must then generally be added here, which you can usually determine by executing the above SELECTs in a comparable R/3 system (i.e. SVI system where the issue is ok).

The default value can be added for a column with the following statement:

ALTER TABLE SAPSR3.SDBAD MODIFY BEG DEFAULT ' ';

ALTER TABLE SAPSR3.SDBAD MODIFY FUNCT DEFAULT ' ';

ALTER TABLE SAPSR3.SDBAD MODIFY SYSID DEFAULT ' ';

ALTER TABLE SAPSR3.SDBAD MODIFY POS DEFAULT '0000';

ALTER TABLE SAPSR3.SDBAD MODIFY LINE DEFAULT ' ';

and now ORA-01400 is missing.

But the problem is for all 150 tables that I converted to IOT !!!!

I suppose that it is a bug of the conversion procedure?

Mario

[ACE-SAP · Accepted solution]

Hi

The script iotc from note  1856270 - Performance improvements for tables with single unique index is creating the IOT table using a CTAS command (create table as select) , and this command is not copying the default value (for this you should use dbms_metadata.get_ddl)

So it indeed looks like a mistake... that might has not been discovered yet...

You could use the here under script to fix this point

Run it on a system where the default values are still present it will generate the commands to recreate the default value clause for all the fields of the converted tables. Just add the list of tables in the "in" clause. Credits to Tom Kyte for the function  (https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:839298816582)

Regards

create or replace function get_col_default (
 p_owner in all_tab_cols.owner%type,
 p_table_name in all_tab_cols.table_name%type,
 p_column_name in all_tab_cols.column_name%type
 )
 return varchar2
 as
         l_data_default LONG;
 begin
         select data_default into l_data_default
           from all_tab_cols
          where owner = p_owner
            and table_name = p_table_name
            and column_name = p_column_name;

         return substr( l_data_default, 1, 4000 );
 end;
 /

set pages 1000 lines 200
select 'alter table sapsr3.' || TABLE_NAME || ' modify ' || COLUMN_NAME || ' default ' || get_col_default( owner, table_name, column_name) || ';'
 from all_tab_cols where table_name in ('SDBAD', 'JEST', 'VBOX') and get_col_default( owner, table_name, column_name ) is not null;

[Former Member]

F.Y.I.

I created an incident with SAP after performing the conversion to IOT using the Oracle package iotc. The result was that all the default values of the tables columns were missing. The Oracle developer has fixed the package. I tested it and it worked successfully. I guess the attachment to #1856270 will be updated in due course.

Regards John

[janos_mucsi-besze]

Hi Mario,

SDBAD table is an important table of BR*Tools. I don't see any points to convert this table to IOT.

By the way, Br*Tools sometimes writes NULL values into some columns, so the error is expected after you converted this table to IOT.

Solution is to revert this table back to normal hash table.

Regards,

János

[mario_bisonti2]

Hallo Janos.

Note that if I revert to normal table, with the script SDBAD_IOTC_UNDO.sql , the problem still happens because it still use the statement "Create AS select ..." so the old default values are lost.

I think that the solution is to create a script, as suggested by Yves Kerdac" to restore default values for all my 150 tables.

However, I am waiting for SAP support what they tell me.
But SAP doesn't answer...

Thanks

[mario_bisonti2]

Ok, I applied the workaround to re apply the default for the tables converted to IOT.

Now SAP will  check because it could be a bug on the IOT conversion procedure.

Thanks a lot.

Mario