DB2-Express-C

AmeriTrak · #1 (**permalink**) 12-05-09, 03:25

I am using JDBC and DB2-Express-C for a project. I have everything loaded, but my tables will not populate.

Can anyone tell me how to populate tables under DB2-Express-C?

I need to get this project up and running by Dec. 7th.

Thanks
Jeff
AmeriTrak

shammat · #2 (**permalink**) 12-05-09, 03:57

What do you mean with "everything loaded"?
What do you mean with "not populate"?
How do you insert the data into the tables?
Can you run the statements outside of your program?
How do check if your data is there?
How do your tables look like?
Do you get any error message?

AmeriTrak · #3 (**permalink**) 12-05-09, 18:38

Hi Shammat --

Thanks for answering.

..."everything loaded" means that I have installed a DB2 Express C system on a Windows XP (SP3) machine, including IBM's Data Studio.

..."not populate" means the following: I am able to create my entire schema using the file 'bat_CRE_Db' (included) by submitting it as a script using IBM's Control Center tool. This script also populates some rows into my ParameterInfo table. I'm then able to successfully query this table using IBM's tools, but my Java program returns an "RS is null" message when I submit the same simple query dynamically through JDBC. Also, none of my inserts are working. That is, I am storing every GPS point (NEMA sentence) returned by my embedded GPS receiver into the GpsInfo table, but no rows are populating.

The strange thing is... I'm not receiving any database, SQL or JDBC errors. Only silence from DB2. However, if I mess with the connection parameters (changing or removing my connect-time un / pw parameter, for example) I DO get errors from JDBC, etc.

...How do you insert the data into the tables? Some of my code is attached. The file extensions have all been changed to .txt for ease of attachement. The file with app- prefix are my Java classes used to implement this project. Those with lib- prefix are from my utility library called JeffUtil that I've been using successfully for years against several other database's.

...Can you run the statements outside of your program? Yes, as I've described. This is equally strange, as IBM's tools are all written in Java using JDBC.

...How do check if your data is there? I do a tool-based query against ParameterInfo: "select * from ParameterInfo order by prmRecId;" and all rows return.

...How do your tables look like? I've attached my schema-generating batch file so you can see how simple my tables really are.

...Do you get any error message? As mentioned, no errors from DB2, SQL or JDBC. Only one error from my application when I test to see if the JDBC ResustSet returned object (instance) is null or not.

Questions:
-- Do I need to create my own 'schema'? with CREATE SCHEMA ... The docs indicate this would give me "user" assigned priv's to the subsequent tables.
-- Do I have an access or permissions issue with the 'atOps0' DB or any of the member tables. That is, could it be a permisions issue that's not letting me select rows from ParameterInfo or insert rows into GpsInfo?
-- To follow the last questions, do I need to include some GRANT statements in my script to ensure permissions get assigned?
-- I'm running Windows XP (SP3) with Java 1.6.0_17. DB2 Express C is the very latest, and IBM's JDBC is included. The DB2 install builds the PATH and CLASSPATH designators for you. Could there be a version issue with JDBC and my code???

Thanks for your interest in my problem. I hope it's something stupid in my code, and not something larger with the OS or DB2.

Best regards, Jeff E.

AmeriTrak · #4 (**permalink**) 12-05-09, 18:39

Hi Shammat --

Here's the last file attachement. There we're 6 files, total.

Please let me know if there is any further info required.

Thanks,
Jeff E
AmeriTrak

Marcus_A · #5 (**permalink**) 12-05-09, 20:59

If you don't have permission to connect to the database, you will get an SQL error. By default, connection is granted to public. You can check this in the Control Center by querying syscat.dbauth.

If you don't have permission to select or insert to tables, you will also get a SQL error. You can check permissions in syscat.tabauth.

By default, all users have select privledges on the catalog, so try that. This should work for anyone "select * from sysibm.sysdummy1" or "select * from syscat.tables where tabname = '<your_table_in_all_caps>' "

I am looking at your SQL (snipit below), and I have never seen this syntax, such as "LIMIT". Are you sure it works with DB2?

Code:

            // SELECT * FROM db2admin.FromServer WHERE updSw=1 ORDER BY rxRecId LIMIT 10;

            sql [0] = "SELECT * FROM db2admin.FromServer WHERE updSw=1 ORDER BY rxRecId LIMIT ";
            sql [1] = Integer.toString (maxRec);
            sql [2] = ";";

What is this? Looks like MySQL to me. A DB2 connection string is not the same as MySQL:

Code:

        // >Database: AmeriTrakDB, com.mysql.jdbc.Driver, jdbc:mysql://207.67.22.123:3306/atOps3, jeff, spode9, 50000

If DB2 is working with the Control Center, there is nothing wrong with DB2 or the OS.

AmeriTrak · #6 (**permalink**) 12-05-09, 21:00

Hi Shammat

I was able to figure out my problem and am now moving forward on the project.

It was a semi colon issue.

Best regards
Jeff Edelstein

AmeriTrak · #7 (**permalink**) 12-05-09, 21:14

Hi Marcus --

Thanks for your thoughts. You're right... I'd better check on the "LIMIT" stuff for DB2. We've been strong MySQL users up to now, but are now beginning our migration to IBM.

Notice that the MySQL stuff is commented out...

I'm still puzzled by all the permissions stuff -- MySQL is a little simpler in this regard. Should I issue GRANT statements in mt bat_CRE_Db script, just to make sure all permissions are set?

Thanks again!
Jeff
AmeriTrak

Marcus_A · #8 (**permalink**) 12-05-09, 21:33

DB2 uses OS authentication. When you connect to DB2, it must be with a valid Windows user (in your case). If the user is different than the table creator, then you might have to grant access.

You can also grant dbadm access to the entire database for the user id you are accessing the database with in the connection string. Just remember that it must be a valid Windows user, and the valid Windows password for that user.

To grant access with the Control Center (instead of SQL grants), right click on the database name, and then select "Authorities...". If you want to grant access to specific tables, right click on the table name in the Control Center and select "Privledges..".

sathyaram_s · #9 (**permalink**) 12-07-09, 04:41

I am sure you know this link .. Just in case, you dont :

developerWorks : Migration station : DB2 track

Sathyaram

Internet Services

Web Development

Digital Marketing

Consumer Tech