Topic
7 replies Latest Post - ‏2012-12-07T22:01:02Z by SystemAdmin
SystemAdmin
SystemAdmin
2826 Posts
ACCEPTED ANSWER

Pinned topic SQL0206N error when updating model from database

‏2011-12-21T12:33:47Z |
I'm trying to create an Entity Framework model from our DB2 database.

I set up a Data Connection to our database in the Server Explorer in Visual Studio 2010, using “IBM DB2 and IDS Data Provider for .NET”. From there, I can see the tables, views, and stored procedures. This looks correct. I then created an edmx file, and attempted to update the model from the database. This gives me the screen with the data objects, but if I select something and try to add it to the model , it fails, and I get this error:

Unable to generate the model because of the following exception: 'An error occurred while executing the command definition. See the inner exception for details.
ERROR 42703 IBMAS SQL0206N "SYSTEM_CONSTRAINT_SCHEMA" is not valid in the context where it is used.
'.

Here’s the output from testconn40:


c:\temp\vsai\VSAI>testconn40 
"database=usalid08;User ID=xxx;password=xxx;Server= usalid08:446
"   Step 1: Printing version info .NET Framework version: 4.0.30319.239 64-bit DB2 .NET provider version: 9.7.4.4 DB2 .NET file version: 9.7.5.4 Capability bits: ALLDEFINED Build: 20111018 Factory 

for invariant name IBM.Data.DB2 verified Factory 

for invariant name IBM.Data.Informix verified IBM.Data.Informix from DbFactory is the Common Informix .NET provider Elapsed: 5.811   Step 2: Validating db2dsdriver.cfg against db2dsdriver.xsd schema file C:\ProgramData\IBM\DB2\IBMDBCL1\cfg\db2dsdriver.cfg against C:\Program F iles\IBM\IBM DATA SERVER DRIVER\cfg\db2dsdriver.xsd Elapsed: 0.206   Step 3: Connecting using 
"database=usalid08;User ID=xxx;password=xxx;Server=usal id08:446
" Server type and version: AS/400 06.01.0000 Elapsed: 2.364   Step 4: Selecting rows from QSYS2.SYSTABLES to validate existence of packages SELECT * FROM QSYS2.SYSTABLES FETCH FIRST 5 rows only Elapsed: 0.609   Step 5: Calling GetSchema 

for tables to validate existence of schema functions Elapsed: 29.991     Test passed.


I figure it's got to be a configuration issue, but I'm new to this driver, so any help would be appreciated!
Updated on 2012-12-07T22:01:02Z at 2012-12-07T22:01:02Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    2826 Posts
    ACCEPTED ANSWER

    Re: SQL0206N error when updating model from database

    ‏2011-12-21T13:43:03Z  in response to SystemAdmin
    Hi,

    when you say you "create an edmx file", do you mean that you use the EDM wizard to create a model? Or do you create an empty model and then try to update it using model update wizard? If you doing the latter, can you please try using the EDM wizard to create the model from the database, and see if that works.

    Also, can you please check whether the following tables in your database have SYSTEM_CONSTRAINT_SCHEMA column defined:

    QSYS2.SYSCST,
    QSYS2.SYSCHKCST,
    QSYS2.SYSKEYCST,
    QSYS2.SYSREFCST

    You're running against iSeries V6R1, so you should, but, if for some reason, one of these tables doesn't have this column defined, you would get the error you're seeing.

    Regards,
    Alex
    • SystemAdmin
      SystemAdmin
      2826 Posts
      ACCEPTED ANSWER

      Re: SQL0206N error when updating model from database

      ‏2011-12-21T19:51:01Z  in response to SystemAdmin
      Thanks for the quick response!

      I have tried both ways, with the same result. I initially tried with the wizard, and it failed with that error, but created the empty model. I then tried to update the model from the empty model.

      It is missing from SYSKEYCST.
      • SystemAdmin
        SystemAdmin
        2826 Posts
        ACCEPTED ANSWER

        Re: SQL0206N error when updating model from database

        ‏2011-12-21T20:14:21Z  in response to SystemAdmin
        Hi,

        it seems that your server is missing a PTF that added this column to the SYSKEYCST view. Please ask your DBA to install PTF SI29764 on the server you're accessing. This should make the SYSTEM_CONSTRAINT_SCHEMA column available and resolve the issue you're seeing.
        Please note, that if the SYSKEYCST is locked by an application during the install of the PTF it's possible that the view would not be recreated and the new column not added. Thus, it's recommended to install the PTF during the system downtime, or during minimal server load.

        Regards,
        Alex
        • SystemAdmin
          SystemAdmin
          2826 Posts
          ACCEPTED ANSWER

          Re: SQL0206N error when updating model from database

          ‏2012-01-17T18:09:52Z  in response to SystemAdmin
          Installing that PTF didn't work, but we ended up upgrading to 7.1, and that took care of it. Thanks.
  • SystemAdmin
    SystemAdmin
    2826 Posts
    ACCEPTED ANSWER

    Re: SQL0206N error when updating model from database

    ‏2012-03-06T16:30:20Z  in response to SystemAdmin
    We finally got this issue fix in V6R1 by applying PTF SI45392 then doing the following
    1) chgjob ccsid(37)

    Then I ran these API's:
    2) call qsys2/qsqxrlf (DLT QSYS2)
    3) call qsys2/qsqxrlf (CRT QSYS2)
    4) call qsys/qsqsysibm
    5) call qsys/qsqibmChk
    Upgrading to V7R1 wasn't an option for us.
    • SystemAdmin
      SystemAdmin
      2826 Posts
      ACCEPTED ANSWER

      Re: SQL0206N error when updating model from database

      ‏2012-12-07T21:20:38Z  in response to SystemAdmin
      Did IBM give you those instructions? I am running into the same issue as well and do not have the luxury of moving to 7.1.

      Any additional advice given woudl be greatly appreciated.
      I'm receiveing the following message while attempting to model the database on the IBM I.
      Successfully registered the assembly 'System.Data.Entity, Version=4.0.0.0, Culture=neutral, PublicKeyToken=xxxxxxxxxx' in the Web.Config file.
      Unable to generate the model because of the following exception: 'An error occurred while executing the command definition. See the inner exception for details.
      ERROR 42703 IBMAS SQL0206N "SYSTEM_CONSTRAINT_SCHEMA" is not valid in the context where it is used.
      '.
      Loading metadata from the database took 00:00:03.8826783.
      Generating the model took 00:00:01.5411138.
      Added the connection string to the Web.Config file.
      Successfully registered the assembly 'System.Data.Entity, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' in the Web.Config file.
      • SystemAdmin
        SystemAdmin
        2826 Posts
        ACCEPTED ANSWER

        Re: SQL0206N error when updating model from database

        ‏2012-12-07T22:01:02Z  in response to SystemAdmin
        It worked.. we are on V6R1.

        I found that the QSYS2/SYSKEYCST did not have the SYSTEM_CONSTRAINT_SCHEMA column defined. I ran your steps and that fixed the problem. I can now model the database in Entity Framework.
        Thank you.