Topic
3 replies Latest Post - ‏2012-10-30T03:51:24Z by SystemAdmin
SystemAdmin
SystemAdmin
2826 Posts
ACCEPTED ANSWER

Pinned topic IBM DB2 9 SQL0206N EntityCommandExecutionException

‏2012-10-16T15:58:31Z |
I am having trouble with IBM DB2 express c 10.1 and .net Entity Framework 4 to work. I am currently trying to get data from DB2 database using Entity Framework 4.

This is the code:


using (DBEntities ent = 

new DBEntities()) 
{ foreach (Company comp in ent.COMPANIES) 
{ listBox1.Items.Add(comp.Address); 
} 
}


I encountered this exception: "EXTENT1.COMPANY_ID" is not valid in the context where it is used. I am quite sure that there is no "EXTENT1.COMPANY_ID" anywhere in my database only "CMS.COMPANY_ID". Any ideas?
Updated on 2012-10-30T03:51:24Z at 2012-10-30T03:51:24Z by SystemAdmin
  • Arvind_Gupta
    Arvind_Gupta
    58 Posts
    ACCEPTED ANSWER

    Re: IBM DB2 9 SQL0206N EntityCommandExecutionException

    ‏2012-10-24T19:11:50Z  in response to SystemAdmin
    Hi,

    When a query is generated by DB2 .Net Entity Framework runtime, it uses alias name for each tables like EXTENT1, EXTENT2 etc in generated query.

    In order to see what queries are being run by Framework runtime, you can use the db2trc facility (http://pic.dhe.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.admin.trb.doc/doc/c0020800.html) to capture the query text.

    Turn on the trace before running the application and switch it off after application execution.

    Please use the following commands to perform tracing:

    db2trc on -m "..CLI.CLI_scnTranslateSQL.*" -f <trcfile> <--- will start the trace
    • After switching on traces, run your application
    db2trc off <--- will stop the trace
    db2trc fmt <trcfile> <fmtfile> <--- will format the trace into readable format

    Open the <fmtfile> in text editor and you will find queries generated by DB2.Net Entity Framework.

    Please verify executing generated queries in DB2 command window.

    Thanks,
    Arvind
  • Arvind_Gupta
    Arvind_Gupta
    58 Posts
    ACCEPTED ANSWER

    Re: IBM DB2 9 SQL0206N EntityCommandExecutionException

    ‏2012-10-25T18:38:49Z  in response to SystemAdmin
    There is an easy way to see what queries are being generated by Entity Framework runtime by "Tracing IBM Data Server Provider for .NET with System.Diagnostics.Trace"

    Enabling this trace for an application could be accomplished with an application.config file with the following contents:
    <configuration>
    <system.diagnostics>
    <switches>
    <add name="DB2NMPTRACE" value="1" />
    </switches>
    </system.diagnostics>
    </configuration>

    Please refer: http://pic.dhe.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.swg.im.dbclient.adonet.doc/doc/t0059656.html
  • SystemAdmin
    SystemAdmin
    2826 Posts
    ACCEPTED ANSWER

    Re: IBM DB2 9 SQL0206N EntityCommandExecutionException

    ‏2012-10-30T03:51:24Z  in response to SystemAdmin
    Tracing the generated sql is one step. Its quite obvious that the generated sql is incorrect. The question is how will i change the generated sql at runtime? Thanks for the quick response by the way.