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?

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.

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

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.

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.