Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2011
    Posts
    4

    Unanswered: Convert view to table?

    Hi all!

    I'm faced with a strange problem: I use Db2 Express-C as a DB for a Java program for doing data mining.

    The Mining Framework enforces the use of a table as datasource for the mining.
    However my data exists in a complex dynamic view, created by the java program.

    Although the mining framework just executes the query "select * from base", base has to be a table, not a view in DB2 in order to run properly.

    Does anyone know how to convert an existing view (attributes not kown in advance) into a real table in DB2 without coding the create statement manually in java?
    Last edited by digu; 10-11-11 at 07:55.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You can use "CREATE TABLE mytable LIKE myview", although something tells me there must be a better solution - you don't want to maintain two copies of your data.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by digu View Post
    The Mining Framework enforces the use of a table as datasource for the mining.
    I have never heard of such nonsense. In DB2, tables and view are both listed in syscat.tables.

    Does the Mining Framework have some sort of filter set to ignore views? If so, you should see if there is a way to override that.

    Only other way I know is to create a Materialized Query Table using the view definition, but not sure if that is supported in DB2 Express-C. See the DB2 manual for more information.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Sep 2011
    Posts
    4
    Quote Originally Posted by Marcus_A View Post
    I have never heard of such nonsense. In DB2, tables and view are both listed in syscat.tables.

    Does the Mining Framework have some sort of filter set to ignore views? If so, you should see if there is a way to override that.

    Only other way I know is to create a Materialized Query Table using the view definition, but not sure if that is supported in DB2 Express-C. See the DB2 manual for more information.
    Its really strange, I agree with you. I use WEKA as framework, and the dataloading procedure gives the following sql error when using the view as basetable:
    com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-243, SQLSTATE=36001, SQLERRMC=SQL_CURSH200C1, DRIVER=4.12.55

    However it works fine when I use the table instead.
    I regret doubling data as well, but I dont know another solution

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Your original statement is not exactly accurate, and you did not look up the DB2 error message (apparently). You can verify that not all views are excluded by your query tool by creating a simple view that looks like the base table and see if that works.

    Obiously, the query tool is opening a cursor and it needs to do it differently in some cases:

    SQL0243N SENSITIVE cursor "<cursor-name>" cannot be defined for the
    specified SELECT statement.

    Explanation:

    Cursor "<cursor-name>" is defined as SENSITIVE, but the content of the
    SELECT statement requires DB2 to build a temporary result table of the
    cursor, and DB2 cannot guarantee that changes made outside this cursor
    will be visible. This situation occurs when the content of the query
    makes the result table read-only. For example, if the query includes a
    join, the result table is read-only. In these cases, the cursor must be
    defined as INSENSITIVE or ASENSITIVE.

    The statement cannot be processed.

    User response:

    Either change the content of the query to yield a result table that is
    not read-only, or change the type of the cursor to INSENSITIVE or
    ASENSITIVE.

    sqlcode: -243

    sqlstate: 36001
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Sep 2011
    Posts
    4
    Quote Originally Posted by Marcus_A View Post
    Your original statement is not exactly accurate, and you did not look up the DB2 error message (apparently). You can verify that not all views are excluded by your query tool by creating a simple view that looks like the base table and see if that works.

    Obiously, the query tool is opening a cursor and it needs to do it differently in some cases:

    SQL0243N SENSITIVE cursor "<cursor-name>" cannot be defined for the
    specified SELECT statement.

    Explanation:

    Cursor "<cursor-name>" is defined as SENSITIVE, but the content of the
    SELECT statement requires DB2 to build a temporary result table of the
    cursor, and DB2 cannot guarantee that changes made outside this cursor
    will be visible. This situation occurs when the content of the query
    makes the result table read-only. For example, if the query includes a
    join, the result table is read-only. In these cases, the cursor must be
    defined as INSENSITIVE or ASENSITIVE.

    The statement cannot be processed.

    User response:

    Either change the content of the query to yield a result table that is
    not read-only, or change the type of the cursor to INSENSITIVE or
    ASENSITIVE.

    sqlcode: -243

    sqlstate: 36001
    Unfortunately I cant change anything relating to cursor type.

    I tried a simple view and used "select * from simpleview" and I get the error above again. With a simple table it works of course.

    Can I modify the select statement in order to solve the problem relating to the cursor type?
    I just want to select all attributes of the view, the select query itself is simple.

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You may be able to make the view read-only to fix the problem. Revoke all privileges except SELECT on the view (also be sure to revoke any group privileges). Look at SYSCAT.TABAUTH to see what non-select privileges exist on the view, and revoke it.

    You can verify if the view is marked as read only by looking at READONLY column in syscat.views.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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