Error Db2 Sql Error Sqlcode=-204 Sqlstate=42704

new Controller DB Technote (troubleshooting) Problem(Abstract) Administrator is attempting to connect to (or create a new) Controller database, which is based on DB2. Administrator launches com.ibm.db2.jcc.am.sqlsyntaxerrorexception db2 sql error sqlcode=-204 sqlstate=42704 Controller Configuration, and launches the 'Database Conversion Utility'. User clicks 'Create DB'.

Sqlcode=-204 Sqlstate=42704 Sqlerrmc=

An error appears. throws an error that includes "DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=.USER_TABLES". Symptom When the 'Create DB'

Is An Undefined Name.. Sqlcode=-204 Sqlstate=42704

button is pressed: Creating new empty Database Running DB Step: initial Running script batch: script/db2_createDB.batch Running script: script/db2/sequence_xdbtrickle_seq.sql Running script: script/db2/table_xdb00.sql ** ERROR: com.ibm.db2.jcc.am.io: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=DECLARE sSql VARCHAR2(4000);

Sqlstate 42704

BEGIN ;BEGIN-OF-STATEMENT;, DRIVER=3.57.82 ** ERROR: com.ibm.db2.jcc.am.io: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=.USER_TABLES, DRIVER=3.57.82 When the 'Connect' button is pressed: ** ERROR: com.ibm.db2.jcc.am.io: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=.USER_TABLES, DRIVER=3.57.82 Cause There are several possible causes for similar "SQLCODE=-204, SQLSTATE=-42704" errors. TIP: See separate IBM Technote #1681892 for more examples. This Technote specifically relates to the scenario where the cause is that the USER_TABLES sqlcode -204 sqlstate 42704 object does not exist in the DB2 Controller tablespace. In turn, this is because the database was not created when the DB2 server was using/running-in Oracle Compatibility Mode. More Information: "SQLCODE=-204, SQLSTATE=42704" is a DB2 error code indicating 'object does not exist'; in this case, it is indicating that either the USER_TABLES object is not present, or that the user does not have access to read this. USER_TABLES is an Oracle compatibility feature. Therefore the "SQLCODE=-204, SQLSTATE=42704" error (referencing USER_TABLES) typically implies that Oracle Compatibility was not switched on when the Controller Tablespace was created. Environment Controller with DB2 database. Diagnosing the problem Use the command DB2SET to check the DB2 server's settings. Steps: The following is based on DB2 server running on Windows: Click "Start - Programs - IBM DB2 - DB2COPY1 (Default) - Command Line Tools - Command Window" Type "db2set" On a correctly-configured server, the settings should include the following: DB2_DEFERRED_PREPARE_SEMANTICS=YES DB2_COMPATIBILITY_VECTOR=ORA Resolving the problem Ensure that DB2 is set to run in Oracle Compatibility Mode before creating any Controller tablespaces. Modify the DB2 database server to be Oracle compatible. Specifically, reconfigure

SQLERRMC=.USER_TABLES, DRIVER=3.59.81" when using "Database Conversion Utility" Technote (troubleshooting) Problem(Abstract) Administrator creates a new DB2 database, which is going to be the Data Mart database. Administrator sqlcode=-104 logs onto the Controller application server, and launches "Controller Configuration". Inside "Database sqlcode=-104 sqlstate=42601 Connections" administrator creates a connection to the new Data Mart target database. Administrator clicks green "Play" button to launch sqlcode 204 sqlstate 42704 ibm-db2 "Database Conversion Utility" (DbConv.exe). Administrator clicks "Connect". User receives error. Symptom ** ERROR: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=FASTNET.USER_TABLES, DRIVER=3.59.81 Cause There are several possible causes for this error: Scenario http://www-01.ibm.com/support/docview.wss?uid=swg21613531 #1 - DB2 database server has not been configured to be Oracle-compatible. Scenario #2 - DB2 database client has "Enable operating system security for DB2 objects" enabled. Scenario #3 - DB2 database has not been created correctly. Environment Controller Data Mart database hosted on DB2. Diagnosing the problem Scenario #1 Use the command DB2SET to check the DB2 server's settings. Steps: The http://www-01.ibm.com/support/docview.wss?uid=swg21594790 following is based on DB2 server running on Windows: Click "Start - Programs - IBM DB2 - DB2COPY1 (Default) - Command Line Tools - Command Window" Type "db2set" On a correctly-configured server, the settings should include the following: DB2_DEFERRED_PREPARE_SEMANTICS=YES DB2_COMPATIBILITY_VECTOR=ORA Resolving the problem Scenario #1 Modify the DB2 database server to be Oracle compatible. Specifically, reconfigure it so that DB2_COMPATIBILITY_VECTOR=ORA. TIP: This setting may have adverse consequences for other (non-Controller) software, so please check before making the change. Steps: Assuming your DB2 database server is hosted on Windows: 1. Obtain some downtime (nobody using ANY databases hosted on the DB2 server) 2. Shutdown Controller application server (to ensure that there are no connections to the DB2 database) 3. Logon to DB2 database server as an administrator 4. Click "Start - Programs - IBM DB2 - DB2COPY1 (Default) - Command Line Tools - Command Window" 5. Type the following: db2set DB2_COMPATIBILITY_VECTOR=ORA 6. If necessary (if this has not already been done in the past), also type: db2set DB2_DEFERRED_PREPARE_SEMANTICS=YES 7. Stop the DB2 server by running the following command: db2stop 8. Start the DB2 server by

here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About http://stackoverflow.com/questions/24144538/why-do-i-get-sqlcode-204-sqlstate-42704-with-db2-luw-and-websphere-app-server Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the https://community.oracle.com/thread/1327230?start=0 Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Why do I get SQLCODE=-204, SQLSTATE=42704 204 sqlstate with DB2 LUW and WebSphere App Server? up vote 3 down vote favorite There are numerous web pages where people show this message as a symptom (with the value after SQLERRMC and the driver level varying): DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=M51Dev.CUSTOMER, DRIVER=3.61.65 including several here on StackOverflow. The error codes mean that an object -- nearly always a table -- is not found, and the db2 sql error value of the SQLERRMC parameter contains the name of the object in question. In every other occurrence of this I can find, the solution was just that they needed to qualify the table name with the schema name. However, as you can see from the line above, that isn't the case here. The schema is M51Dev, and the table is CUSTOMER, and both of them exist. The environment is WebSphere Application Server (7.0.0.31 in this particular case, but I somehow doubt that's relevant) running on Windows Server 2003 R2, with DB2 9.7.300.3885 on an old XP machine. The datasource is defined in WAS and retrieved via JNDI. the application is written in Java, and our SQL is executed using JDBC via Spring's JdbcTemplates. The datasource definition in WAS includes a custom property called currentSchema, which is set to M51Dev. This is the relevant line of SQL (or one of them, as we get the same symptom for every table): SELECT rundateOverride FROM customer WHERE customerId=1 And here's an extract from the stack trace. I can provide more if it helps. bad SQL grammar [SELECT rundateOverride FROM customer WHERE customerId=1]; nested exception is com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL

Speaker BureauLog inRegisterSearchSearchCancelError: You don't have JavaScript enabled. This tool uses JavaScript and much of it will not work correctly without it enabled. Please turn JavaScript back on and reload this page. Please enter a title. You can not post a blank message. Please type your message and try again. More discussions in Java Database Connectivity (JDBC) All PlacesJavaDatabase ConnectivityJava Database Connectivity (JDBC) This discussion is archived 9 Replies Latest reply on Jan 15, 2008 4:05 PM by 843859 DB2 SQL error: SQLCODE: -204, SQLSTATE: 42704 843859 Jan 15, 2008 2:03 PM I don't get it. When you create a table in DB2, do you have to do anything extra to it to make it so you can do a select on it? I run my Java program with a specified name of a table which has been in the database for a long time. But then I change one single variable, the table name, and it gives me the 42704 error meaning it can't find the table. The problem is I open up Rapid SQL and can see the table that I created but it still gives me the 42704 error when I try to use it in my code. Here's what I'm talking about: ------------------------------------------------------ select * from ------------------------------------------------------------------ So when tableName is the table that's always been in the database, it works fine. But when I change it to the table name I created, it doesn't work. But yet the column and data types are all the same. Any ideas? Here's the method I'm using to call it. ------------------------------------------------------------------------- public Daemon getUsername () throws SQLException { return (Daemon) (sqlMapper.queryForObject("getUsername")); } -------------------------------------------------------------------------------- Here's the error: There is an exception: com.ibatis.common.jdbc.exception.NestedSQLException: --- The error occurred in ..\\Daemon.xml. --- The error occurred while applying a parameter map. --- Check the getUsername-InlineParameterMap. --- Check the statement (query failed). --- Cause: com.ibm.db2.jcc.c.SqlException: DB2 SQL er