Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Question Answered: Do not understand why SQLCODE=-407

    Database server = DB2 10.5.4
    OS: AUX 7.1

    Hi Guys,
    I'm new to DB2 and getting this error:
    Assignment of a NULL value to a NOT NULL column "" is not allowed.. SQLCODE=-407, SQLSTATE=23502, DRIVER=4.18.60
    My insert:
    Code:
    INSERT INTO CB_CATEGORY_MASTER
      SELECT *
        FROM CB_CATEGORY_MASTER_SRC
    Table definition:
    Code:
    CREATE TABLE Cb_Category_Master
    (
       Global_Payor_Id   DECIMAL ( 6 )
     , Category_Number   DECIMAL ( 5 )
     , Description       CHAR ( 50 )
     , Active_Code       CHAR ( 1 )
     , Comments          VARCHAR ( 500 )
     , User_Id           CHAR ( 10 )
     , Program_Id        CHAR ( 10 )
     , Time_Stamp        TIMESTAMP (6)
    )
    ORGANIZE BY COLUMN
    DATA CAPTURE NONE
    ;
    Cb_Category_Master is local table.
    Cb_Category_Master_src is nickname for remote table.
    Please help to understand why?

    PS: Someone provided a stored procedure or function where one would suppy the error codes and it would return more information related to those codes.
    I would be very grateful if anyone can provide that procedure/function.
    Thanks.
    Last edited by LKBrwn_DBA; 06-02-15 at 15:49.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  2. Best Answer
    Posted by db2mor

    "DB2 for i-series skills required here...it's quite a different beast from DB2 on Unix/Windows...
    If you don't get DB2 for i-series specific replies on here, try on an IBM forum on IBM developerworks site."


  3. #2
    Join Date
    Nov 2010
    Posts
    99
    1st which os and version of db2?

    type this in your command window:

    db2level
    describe table Cb_Category_Master

    post results please.

  4. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713
    Thanks for looking into this.
    Database server = DB2 10.5.4
    OS: AUX 7.1
    Code:
    ==> db2level
    DB21085I  This instance or install (instance name, where applicable:
    "db2inst1") uses "64" bits and DB2 code release "SQL10054" with level
    identifier "0605010E".
    Informational tokens are "DB2 v10.5.0.4", "s140813", "IP23616", and Fix Pack
    "4".
    Product is installed at "/opt/IBM/db2/V10.5".
    
    
    ==> db2 "describe table cb_category_master"
    
                                    Data type                     Column
    Column name                     schema    Data type name      Length     Scale Nulls
    ------------------------------- --------- ------------------- ---------- ----- ------
    GLOBAL_PAYOR_ID                 SYSIBM    DECIMAL                      6     0 Yes
    CATEGORY_NUMBER                 SYSIBM    DECIMAL                      5     0 Yes
    DESCRIPTION                     SYSIBM    CHARACTER                   50     0 Yes
    ACTIVE_CODE                     SYSIBM    CHARACTER                    1     0 Yes
    COMMENTS                        SYSIBM    VARCHAR                    500     0 Yes
    USER_ID                         SYSIBM    CHARACTER                   10     0 Yes
    PROGRAM_ID                      SYSIBM    CHARACTER                   10     0 Yes
    TIME_STAMP                      SYSIBM    TIMESTAMP                   10     6 Yes
    
      8 record(s) selected.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #4
    Join Date
    Nov 2010
    Posts
    99
    sorry I am not on this version of db2. I couldn't replicate your issue. I created the tables using your DDL (but had to remove the ORGANIZE BY COLUMN clause:not supported on my 9.7 db). I was able to copy 1 row from Cb_Category_Master_src to Cb_Category_Master.

  6. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713
    Found something, seems to be the query on the source table (nickname) is generating the error:
    Code:
    ==> db2 "describe table cb_category_master_src"
    
                                    Data type                     Column
    Column name                     schema    Data type name      Length     Scale Nulls
    ------------------------------- --------- ------------------- ---------- ----- ------
    GLOBAL_PAYOR_ID                 SYSIBM    DECIMAL                      6     0 No
    CATEGORY_NUMBER                 SYSIBM    DECIMAL                      5     0 No
    DESCRIPTION                     SYSIBM    CHARACTER                   50     0 No
    ACTIVE_CODE                     SYSIBM    CHARACTER                    1     0 No
    COMMENTS                        SYSIBM    VARCHAR                    500     0 No
    USER_ID                         SYSIBM    CHARACTER                   10     0 No
    PROGRAM_ID                      SYSIBM    CHARACTER                   10     0 No
    TIME_STAMP                      SYSIBM    TIMESTAMP                   10     6 No
    
      8 record(s) selected.
    The nickname is being generated with "Nulls=NO" and apparently the source data has null values.
    Any ideas?
    I created the nickname as follows:
    Code:
    CREATE NICKNAME DBPRD.CB_CATEGORY_MASTER_SRC FOR FAS400.DBPRD.CB_CATEGORY_MASTER;
    Did I miss any options?
    Last edited by LKBrwn_DBA; 06-02-15 at 18:16.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  7. #6
    Join Date
    May 2012
    Location
    Canberra, Australia
    Posts
    176
    Provided Answers: 6
    I'm on 10.5.5 and created a nickname using "CREATE NICKNAME MCLAUCA.NICKNAME1 FOR DCAPDEVA.SYNCSOFT.ACCTHIST" and it reflects the exact column defs of the remote table so I'm a bit puzzled as to why you have something different.
    Andy

    "All parts should go together without forcing. You must remember that the parts you are reassembling were disassembled by you. Therefore, if you can’t get them together again, there must be a reason. By all means, do not use hammer.” — IBM maintenance manual, 1975 "

  8. #7
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Which options did you use for the 'create server' and 'create user mapping' commands?
    If your remote server is i-Series, are you connecting with the driver-package or db2-connect?

    Remember that you can also try the 'create nickname' with enumerated column-list (including the not null) - instead of simply referencing the remote object name - that might be a quick workaround. See the docs for details of the syntax.

  9. #8
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713
    Quote Originally Posted by db2mor View Post
    Which options did you use for the 'create server' and 'create user mapping' commands?
    If your remote server is i-Series, are you connecting with the driver-package or db2-connect?

    Remember that you can also try the 'create nickname' with enumerated column-list (including the not null) - instead of simply referencing the remote object name - that might be a quick workaround. See the docs for details of the syntax.
    Sorry if I'm slow, I knew DB2 version Jurassic period (mainframe). Now I'm Oracle DBA.
    Our DB Architect in charge of the data warehouse (the DB with issue) left me this monster.

    I did see the enumerated column-list but I did not see how it would refer to the specific table because it only has the 'FOR SERVER" option (for example):
    Code:
    CREATE NICKNAME customers  <== The If this is the source name I need nickname to be different
         (id      VARCHAR(5)   OPTIONS(XPATH './@id'),        <== What are these?
          name    VARCHAR(16)  OPTIONS(XPATH './/name'),
          address VARCHAR(30)  OPTIONS(XPATH './/address/@street'),
          cid     VARCHAR(16)  OPTIONS(PRIMARY_KEY 'YES'))
          FOR SERVER xml_server              <== See refers only to the server, what about the source table?
          OPTIONS                                 <== And what are these?
            (DIRECTORY_PATH '/home/db2user',
             XPATH '//customer',
             STREAMING 'YES')
    Except for trying to create this nickname, I did not issue any of the commands but here is the DDL:
    Code:
    CREATE WRAPPER "DRDA400" LIBRARY 'libdb2drda.a' OPTIONS (
    		ADD DB2_FENCED 'N'
    	);
    
    CREATE SERVER "FAS400"
    	TYPE "DB2/400"
    	VERSION '7.1'
    	WRAPPER "DRDA400"
    	AUTHORIZATION "DB2INST1"
    	PASSWORD "xxxxxxx"
    	OPTIONS (
    		ADD DBNAME 'elkabrwn', 
    		PASSWORD 'Y', 
    		PUSHDOWN 'Y'
    	);
    
    CREATE USER MAPPING FOR "DB2INST1" SERVER "FAS400" OPTIONS (
    		ADD REMOTE_AUTHID 'xxxxxxx', 
    		REMOTE_PASSWORD 'xxxxxxxxxx'
    	);
    
    
    CREATE NICKNAME "DBPRD"."CB_CATEGORY_MASTER_SRC" FOR "FAS400"."DBPRD"."CB_CATEGORY_MASTER";
    Thank you a mil for your help!
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  10. #9
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    DB2 for i-series skills required here...it's quite a different beast from DB2 on Unix/Windows...
    If you don't get DB2 for i-series specific replies on here, try on an IBM forum on IBM developerworks site.

  11. #10
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713
    Thank you db2mor.
    I did not find anything on DB2 Forums (or Google), so I created an IBM Service Request.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  12. #11
    Join Date
    May 2010
    Location
    India
    Posts
    84
    Provided Answers: 2
    You can try using alter nickname command after creating the nickname to remove not null setting. Not sure whether dropping "Not NULL" is allowed thru the command, you can try and see whether it works.

    Check the link


    http://www-01.ibm.com/support/knowle...2-7-18&lang=no

    Satya...
    Last edited by stiruvee; 06-04-15 at 06:58.

  13. #12
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713
    Quote Originally Posted by stiruvee View Post
    You can try using alter nickname command after creating the nickname to remove not null setting. Not sure whether dropping "Not NULL" is allowed thru the command, you can try and see whether it works.
    . . .
    Satya...
    Thank you Satya, this is good info, I will try it.
    The weird thing is all nicknames tables have all columns as not-null and do not fail.
    Anyway I opened an SR (PMR?) with IBM, lets see what they say.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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