I have an IBM IDAA set up where I created an accelerator-only table with a CREATE statement:
CREATE TABLE aot_table (
"col_1" decimal(11,0) NOT NULL ,
"col_2" smallint,
"col_3" smallint,
) IN ACCELERATOR my_accel;
I then loaded it, manually with INSERTS, with test data. When I query it with:
SELECT * from MY_USER.aot_table;
I get data as expected.
But if I try querying col_1 directly, like this:
SELECT col_1 from MY_USER.aot_table;
I get an error stating:
`select col_1 from MY_USER.aot_table
COL_1 IS NOT VALID IN THE CONTEXT WHERE IT IS USED. SQLCODE=-206, SQLSTATE=42703, DRIVER=4.24.92`
Does anyone know why this happens?
If you created the table with double-quotes around the column names and lowercase colnames, as shown in your question, then your queries must also double-quote the column-names and match colum-name case.
Otherwise Db2 will fold unquoted column names to uppercase which will not match the lowercase column names, which will result in exceptions.
So try select "col_1" from MY_USER.aot_table , assuming that the tabschema is also MY_USER. Notice the quoted column name.
If you do not want to double-quote your column names in your queries then do not use double-quotes around column names in create table statement. Unuoted column names in queries will then fold to uppercase which is usually more convenient for SQL.