Author Topic: db2 table and colume name  (Read 9568 times)

huli

  • Newbie
  • *
  • Posts: 1
  • Karma: +0/-0
db2 table and colume name
« on: February 11, 2017, 04:31:20 AM »
Hi,
   The sql file of db2 to create table is tha mostly same with postgresql, and can load db data.
   But can not read by the appserver. The reason is that:
        Postgresql will change the table name to lower case, but the db2 will change to upper case.
        if execute sql which table name without  double quote, there is no problem. for example:
             select * from c_customer
       but if execute
             select * from "c_customer"
       postgresql can execute successfully because the table name is the same,
       but db2 will fail, because the table name in db2 is C_CUSTOMER

       Then , the appserver execute sql with double quote, so can work well with postgresql,
       but will fail with db2 v9.7

       How to resolve this problem without modify table name in db2? Is there any config in appserver to suit db2?

lroderic

  • Moderator
  • Full Member
  • *****
  • Posts: 167
  • Karma: +6/-0
Re: db2 table and colume name
« Reply #1 on: February 13, 2017, 06:58:24 PM »
Hello. Tuning the application stack is beyond the scope of support that we can provide for the benchmark. I suggest you download a tarball archive of one or more submissions that used DB2 and look at the db creation scripts within.

Lisa

Zboncak

  • Newbie
  • *
  • Posts: 1
  • Karma: +0/-0
Re: db2 table and colume name
« Reply #2 on: August 18, 2021, 06:35:29 AM »
I am currently reading an an Oracle text book for the 1Z0-071 certification and it suggests that employees, customers, and vendors would all use first_name, last_name, etc.

In the tables I've designed to date, I would use emp_fname, emp_lname, cust_fname, cust_lname, and so forth.

Is there any standard on which naming convention is preferable? I've always thought that using column names which are dirived from the table would be more clear in the event similar columns are referenced in a join. It also provides a sort of error check in case you select the wrong table.

I'm just curious if there is an industry standard on this issue, or if it's a matter of preference?
« Last Edit: August 23, 2021, 10:40:09 AM by lroderic »

lroderic

  • Moderator
  • Full Member
  • *****
  • Posts: 167
  • Karma: +6/-0
Re: db2 table and colume name
« Reply #3 on: August 18, 2021, 09:25:31 AM »
For SPECvirt_sc2013, you use the database schema defined in the SPECjAppServer2004 kit. For non-SPEC projects, follow your database vendor's guidance.

Good luck on your certification.

« Last Edit: August 18, 2021, 10:18:59 AM by lroderic »

haliasa

  • Newbie
  • *
  • Posts: 2
  • Karma: +0/-3
Re: db2 table and colume name
« Reply #4 on: March 08, 2022, 07:09:26 AM »
First, specify the name of the table to which you want to add the new column in the ALTER TABLE clause. Second, specify the new column including name, data type, and column constraint in the ADD COLUMN clause