Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Apr 2015
    Posts
    13

    Unanswered: While select on table it gives SQL0407N SQLSTATE=23502 error

    Hi,

    I have 2 DB's

    1. M1
    2. M2

    I have created 1 table and inserted record into M1 DB. I'm able to fetch record by selecting on table.

    Now, I have created Nickname for the above table into M2 DB, but unable to fetch record giving below error.
    SQL0407N Assignment of a NULL value to a NOT NULL column "" is not allowed.
    SQLSTATE=23502

    Whereas, It is working fine for all other tables by following same above procedures.

    Appreciate your help..!!!

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    please be more specific and provide all details
    do you get the error with select or insert ?
    specify the steps you have gone through to get this nickname ?
    There is something called PD/PSI (problem determination / problem source identification) that you can download and will resolve all your problems..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Apr 2015
    Posts
    13
    Hi Przytula,

    I'm not inserting record into the table, I'm getting error while selecting on table.

    I followed below steps.

    M1 DB
    1. Created table M1.Milind and inserted record into the table.
    2. while issuing select * from M1.Milind, I'm getting 27 record(s) selected.

    M2 DB
    1. I created Nickname for the M1.Milind table in M2 DB
    db2 "create nickname M2.Milind_1 for M1.M1.Milind"
    Created successfully.

    2. While selecting on M2.Milind_1 table I'm getting below error
    db2 "select * from M2_Milind_1"
    SQL0407N Assignment of a NULL value to a NOT NULL column "" is not allowed.
    SQLSTATE=23502

  4. #4
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Are both databases DB2 databases?
    Specify the exact version+fixpack of each database.
    Which wrapper are you using?

  5. #5
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    have you checked the column definition of the nickname compared to the ddl of source table
    something can be wrong
    there is command : alter nickname that could correct this
    if you used above command and table has not changed in between there is problem
    as you do not indicate db2version and level .. it might be needed to install latest fixpack or open a pmr if this is a new problem (which I doubt)
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  6. #6
    Join Date
    Apr 2015
    Posts
    13
    Hi,

    Both the databases are db2 and below is the version

    DB21085I This instance or install (instance name, where applicable:
    "db2inst5") uses "64" bits and DB2 code release "SQL09079" with level
    identifier "080A0107".
    Informational tokens are "DB2 v9.7.0.9", "s131204", "IP23561", and Fix Pack
    "9".
    Product is installed at "/db2/ibm/db2/V9.7".

    I have also opened PMR.. Hoping for the best result.

    My dout is why it is failing for 1 table, whereas I'm able to fetch data by following same procedure for the other tables ( strange )

  7. #7
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Are both databases in the same DB2 instance, or in different instances?
    Post the DDL you used to create the wrapper, the full 'create server' (including the options, hide the password of course), and what user mapping(s) you created.

  8. #8
    Join Date
    Apr 2015
    Posts
    13
    Hi,

    Yes, Both the DB's are on same instance.

    I have created Wrapper, Server & Mapping on M2 DB by using following script.

    DDL Statements for WRAPPER
    ---------------------------
    CREATE WRAPPER "DRDA" LIBRARY 'libdb2drda.so' OPTIONS (DB2_FENCED 'N' );

    DDL Statements for SERVER
    ---------------------------
    CREATE SERVER "M1"
    TYPE DB2/UDB
    VERSION '9.7'
    WRAPPER "DRDA"
    AUTHORIZATION ""
    PASSWORD ""
    OPTIONS
    (DATE_COMPAT 'N'
    ,DB2_CONCAT_NULL_NULL 'Y'
    ,DB2_VARCHAR_BLANKPADDED_COMPARISON 'Y'
    ,DBNAME 'M1'
    ,NODE 'M1_instance'
    ,NO_EMPTY_STRING 'N'
    ,NUMBER_COMPAT 'N'
    ,SAME_DECFLT_ROUNDING 'Y'
    ,VARCHAR2_COMPAT 'N'
    );

    DDL Statements for USER MAPPING
    --------------------------------
    CREATE USER MAPPING FOR PUBLIC
    SERVER "M1
    OPTIONS (REMOTE_AUTHID 'M1_instance' REMOTE_PASSWORD ' ' );

  9. #9
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    have you compared column definition for source and nickname ?
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  10. #10
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Suggestion: drop the nickname, commit, connect reset, connect again, re-create nickname, retry the SELECT.

    Interesting that IBM did not document DB2_CONCAT_NULL_NULL and DB2_VARCHAR_BLANKPADDED_COMPARISON server options yet, although they automatically get used.

    Separately: I normally use DB2_MAXIMAL_PUSHDOWN=Y (default is N) when both source+target are same DB2 version+platform.

  11. #11
    Join Date
    Apr 2015
    Posts
    13
    Hi,

    I tried what you posted.. But No luck.

    @ przytula
    I'm creating Nickname for table by using simple command
    Create Nickname M2.Milind_1 for M1.M1.Milind

  12. #12
    Join Date
    Apr 2015
    Posts
    13
    Hi,

    I tried what you said.. But no luck.

    Hi przytula,

    I created Nickname by simple command
    Create nickname M2.Milind_1 for M1.M1.Milind

  13. #13
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Strange, it works for me (on V9.7.10).
    Post what messages appear in the db2diag.log when you get the SQL0407N
    Post the DDL for the table M1.Milind that db2look -d M1 -e will show.

  14. #14
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    I keep coming back until you provide the answer :
    have you compared column definition for source and nickname ?
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  15. #15
    Join Date
    Apr 2015
    Posts
    13
    Hi,

    CREATE TABLE M1.Milind (
    "ID" VARCHAR(48) NOT NULL ,
    "EXTID" VARCHAR(255) NOT NULL ,
    "USERNAME" VARCHAR(255) NOT NULL ,
    "PASSPHRASE" VARCHAR(255) NOT NULL ,
    "FULLNAME" VARCHAR(255) NOT NULL ,
    "EMAILADDRESS" VARCHAR(255) NOT NULL WITH DEFAULT '' ,
    "DATECREATED" TIMESTAMP NOT NULL ,
    "LOCALE" VARCHAR(20) ,
    "TIMEZONE" VARCHAR(50) ,
    "ISENABLED" SMALLINT NOT NULL WITH DEFAULT 1 ,
    "LASTLOGIN" TIMESTAMP ,
    "STATE" INTEGER NOT NULL WITH DEFAULT 0 )

    I Diaglog file no error gets log into the file upon firing the select query on table.


    @ przytula,

    I ran simple command for creatin Nickname for above table in M2 DB
    Create Nickname M2.Milind_1 for M1.M1.Milind

    So in this case i do not need to compare my column defination, Isn't it..?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •