Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQL compatibility with DB2

Posted on 2002-06-19
6
593 Views
Last Modified: 2008-02-26
I have a series of queries in a coldfusion script that I originally used to create the tables in a SQLServer 2000 database. I now want to create the exact same set of tables in a DB2 data base on an AS400 machine. I would like you to look at each of the queries below and tell me what kind of syntax changes will need to be made to get the script to work with the DB2 databse or if it will work fine.
For those of you who aren't familiar with coldfusion, the SQL statements will be inbetween "<cfquery>" and "</cfquery>" like this:

<cfquery (tag attributes)>
SQL Statement goes here
</cfquery>

Here are the queries:
--------------------------------------------------------
<Cfquery name="CreateTable_CM_Instances" datasource="#Request.Datasource#">
CREATE TABLE [CM_Instances] (
      [Instance_ID] [bigint] IDENTITY (1, 1) NOT NULL ,
      [Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [Support_Email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [IP_Address_external] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [IP_Address_internal] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Domain_Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
</cfquery>

<Cfquery name="CreateTable_CM_Gallery_Types" datasource="#Request.Datasource#">
CREATE TABLE [CM_Gallery_Types] (
      [Gallery_Type_ID] [bigint] IDENTITY (1, 1) NOT NULL ,
      [Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
</cfquery>

<Cfquery name="CreateTable_CM_Languages" datasource="#Request.Datasource#">
CREATE TABLE [CM_Languages] (
      [Language_ID] [bigint] IDENTITY (1, 1) NOT NULL ,
      [Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
</cfquery>

<Cfquery name="CreateTable_CM_Roles" datasource="#Request.Datasource#">
CREATE TABLE [CM_Roles] (
      [Role_ID] [bigint] IDENTITY (1, 1) NOT NULL ,
      [Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
</cfquery>

<Cfquery name="CreateTable_CM_Users" datasource="#Request.Datasource#">
CREATE TABLE [CM_Users] (
      [User_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [First_Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [Last_Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [Email_Address] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Password] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
</cfquery>

<Cfquery name="CreateTable_CM_Galleries" datasource="#Request.Datasource#">
CREATE TABLE [CM_Galleries] (
      [Gallery_ID] [bigint] IDENTITY (1, 1) NOT NULL ,
      [Gallery_Type_ID] [bigint] NOT NULL ,
      [Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
</cfquery>

<Cfquery name="CreateTable_CM_Languages_2_Instances" datasource="#Request.Datasource#">
CREATE TABLE [CM_Languages_2_Instances] (
      [Instance_ID] [bigint] NOT NULL ,
      [Language_ID] [bigint] NOT NULL ,
      [Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
</cfquery>

<Cfquery name="CreateTable_CM_Sections" datasource="#Request.Datasource#">
CREATE TABLE [CM_Sections] (
      [Section_ID] [bigint] IDENTITY (1, 1) NOT NULL ,
      [Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [Nav_Flg] [bit] NOT NULL ,
      [Parent_Section_ID] [bigint] NULL ,
      [Instance_ID] [bigint] NOT NULL ,
      [Sort_Order] [int] NOT NULL
) ON [PRIMARY]
</cfquery>

<Cfquery name="CreateTable_CM_Users_2_Instances" datasource="#Request.Datasource#">
CREATE TABLE [CM_Users_2_Instances] (
      [User_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [Instance_ID] [bigint] NOT NULL
) ON [PRIMARY]
</cfquery>

<Cfquery name="CreateTable_CM_Users_2_Roles" datasource="#Request.Datasource#">
CREATE TABLE [CM_Users_2_Roles] (
      [User_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [Role_ID] [bigint] NOT NULL
) ON [PRIMARY]
</cfquery>

<Cfquery name="CreateTable_CM_Gallery_Items" datasource="#Request.Datasource#">
CREATE TABLE [CM_Gallery_Items] (
      [Gallery_Item_ID] [bigint] IDENTITY (1, 1) NOT NULL ,
      [Gallery_ID] [bigint] NOT NULL ,
      [Description] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [File_Name_1] [varchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [File_Name_2] [varchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
</cfquery>

<Cfquery name="CreateTable_CM_PAGE_CONTENT" datasource="#Request.Datasource#">
CREATE TABLE [CM_PAGE_CONTENT] (
      [Page_ID] [bigint] IDENTITY (1, 1) NOT NULL ,
      [Section_ID] [bigint] NOT NULL ,
      [Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [Details] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [Content] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [Nav_Flg] [bit] NOT NULL ,
      [Gallery_ID] [bigint] NULL ,
      [Sort_Order] [int] NOT NULL
) ON [PRIMARY]
</cfquery>

<Cfquery name="Create_PK_CM_Instances" datasource="#Request.Datasource#">
ALTER TABLE [CM_Instances] WITH NOCHECK ADD
      CONSTRAINT [PK_CM_Instances] PRIMARY KEY  CLUSTERED
      (
            [Instance_ID]
      )  ON [PRIMARY]
</cfquery>

<Cfquery name="Create_PK_CM_Gallery_Types" datasource="#Request.Datasource#">
ALTER TABLE [CM_Gallery_Types] WITH NOCHECK ADD
      CONSTRAINT [PK_CM_Gallery_Types] PRIMARY KEY  CLUSTERED
      (
            [Gallery_Type_ID]
      )  ON [PRIMARY]
</cfquery>

<Cfquery name="Create_PK_CM_Languages" datasource="#Request.Datasource#">
ALTER TABLE [CM_Languages] WITH NOCHECK ADD
      CONSTRAINT [PK_CM_Languages] PRIMARY KEY  CLUSTERED
      (
            [Language_ID]
      )  ON [PRIMARY]
</cfquery>

<Cfquery name="Create_PK_CM_Roles" datasource="#Request.Datasource#">
ALTER TABLE [CM_Roles] WITH NOCHECK ADD
      CONSTRAINT [PK_CM_Roles] PRIMARY KEY  CLUSTERED
      (
            [Role_ID]
      )  ON [PRIMARY]
</cfquery>

<Cfquery name="Create_PK_CM_Users" datasource="#Request.Datasource#">
ALTER TABLE [CM_Users] WITH NOCHECK ADD
      CONSTRAINT [PK_CM_Users] PRIMARY KEY  CLUSTERED
      (
            [User_ID]
      )  ON [PRIMARY]
</cfquery>

<Cfquery name="Create_PK_CM_Galleries" datasource="#Request.Datasource#">
ALTER TABLE [CM_Galleries] WITH NOCHECK ADD
      CONSTRAINT [PK_CM_Galleries] PRIMARY KEY  CLUSTERED
      (
            [Gallery_ID]
      )  ON [PRIMARY]
</cfquery>

<Cfquery name="Create_PK_CM_Languages_2_Instances" datasource="#Request.Datasource#">
ALTER TABLE [CM_Languages_2_Instances] WITH NOCHECK ADD
      CONSTRAINT [PK_CM_Languages_2_Instances] PRIMARY KEY  CLUSTERED
      (
            [Instance_ID],
            [Language_ID]
      )  ON [PRIMARY]
</cfquery>

<Cfquery name="Create_PK_CM_Sections" datasource="#Request.Datasource#">
ALTER TABLE [CM_Sections] WITH NOCHECK ADD
      CONSTRAINT [PK_CM_Sections] PRIMARY KEY  CLUSTERED
      (
            [Section_ID]
      )  ON [PRIMARY]
</cfquery>

<Cfquery name="Create_PK_CM_Users_2_Instances" datasource="#Request.Datasource#">
ALTER TABLE [CM_Users_2_Instances] WITH NOCHECK ADD
      CONSTRAINT [PK_CM_Users_2_Instances] PRIMARY KEY  CLUSTERED
      (
            [User_ID],
            [Instance_ID]
      )  ON [PRIMARY]
</cfquery>

<Cfquery name="Create_PK_CM_Users_2_Roles" datasource="#Request.Datasource#">
ALTER TABLE [CM_Users_2_Roles] WITH NOCHECK ADD
      CONSTRAINT [PK_CM_Users_2_Roles] PRIMARY KEY  CLUSTERED
      (
            [User_ID],
            [Role_ID]
      )  ON [PRIMARY]
</cfquery>

<Cfquery name="Create_PK_CM_Gallery_Items" datasource="#Request.Datasource#">
ALTER TABLE [CM_Gallery_Items] WITH NOCHECK ADD
      CONSTRAINT [PK_CM_Gallery_Items] PRIMARY KEY  CLUSTERED
      (
            [Gallery_Item_ID]
      )  ON [PRIMARY]
</cfquery>

<Cfquery name="Create_PK_CM_PAGE_CONTENT" datasource="#Request.Datasource#">
ALTER TABLE [CM_PAGE_CONTENT] WITH NOCHECK ADD
      CONSTRAINT [PK_CM_PAGE_CONTENT] PRIMARY KEY  CLUSTERED
      (
            [Page_ID]
      )  ON [PRIMARY]
</cfquery>

<Cfquery name="Create_DF_CM_Sections_Nav_Flg" datasource="#Request.Datasource#">
ALTER TABLE [CM_Sections] WITH NOCHECK ADD
      CONSTRAINT [DF_CM_Sections_Nav_Flg] DEFAULT (1) FOR [Nav_Flg]
</cfquery>
<Cfquery name="CreateTables" datasource="#Request.Datasource#">
ALTER TABLE [CM_PAGE_CONTENT] WITH NOCHECK ADD
      CONSTRAINT [DF_CM_PAGE_CONTENT_Nav_Flg] DEFAULT (1) FOR [Nav_Flg]
</cfquery>

<Cfquery name="Create_FK_CM_Galleries_CM_Gallery_Types" datasource="#Request.Datasource#">
ALTER TABLE [CM_Galleries] ADD
      CONSTRAINT [FK_CM_Galleries_CM_Gallery_Types] FOREIGN KEY
      (
            [Gallery_Type_ID]
      ) REFERENCES [CM_Gallery_Types] (
            [Gallery_Type_ID]
      )
</cfquery>

<Cfquery name="Create_FK_CM_Languages_2_Instances_CM_Languages" datasource="#Request.Datasource#">
ALTER TABLE [CM_Languages_2_Instances] ADD
      CONSTRAINT [FK_CM_Languages_2_Instances_CM_Instances] FOREIGN KEY
      (
            [Instance_ID]
      ) REFERENCES [CM_Instances] (
            [Instance_ID]
      ),
      CONSTRAINT [FK_CM_Languages_2_Instances_CM_Languages] FOREIGN KEY
      (
            [Language_ID]
      ) REFERENCES [CM_Languages] (
            [Language_ID]
      )
</cfquery>

<Cfquery name="Create_FK_CM_Sections_CM_Instances" datasource="#Request.Datasource#">
ALTER TABLE [CM_Sections] ADD
      CONSTRAINT [FK_CM_Sections_CM_Instances] FOREIGN KEY
      (
            [Instance_ID]
      ) REFERENCES [CM_Instances] (
            [Instance_ID]
      ),
      CONSTRAINT [FK_CM_Sections_CM_Sections] FOREIGN KEY
      (
            [Parent_Section_ID]
      ) REFERENCES [CM_Sections] (
            [Section_ID]
      )
</cfquery>

<Cfquery name="Create_FK_CM_Users_2_Instances_CM_Instances" datasource="#Request.Datasource#">
ALTER TABLE [CM_Users_2_Instances] ADD
      CONSTRAINT [FK_CM_Users_2_Instances_CM_Instances] FOREIGN KEY
      (
            [Instance_ID]
      ) REFERENCES [CM_Instances] (
            [Instance_ID]
      ),
      CONSTRAINT [FK_CM_Users_2_Instances_CM_Users] FOREIGN KEY
      (
            [User_ID]
      ) REFERENCES [CM_Users] (
            [User_ID]
      )
</cfquery>

<Cfquery name="Create_FK_CM_Users_2_Roles_CM_Roles" datasource="#Request.Datasource#">
ALTER TABLE [CM_Users_2_Roles] ADD
      CONSTRAINT [FK_CM_Users_2_Roles_CM_Roles] FOREIGN KEY
      (
            [Role_ID]
      ) REFERENCES [CM_Roles] (
            [Role_ID]
      ),
      CONSTRAINT [FK_CM_Users_2_Roles_CM_Users] FOREIGN KEY
      (
            [User_ID]
      ) REFERENCES [CM_Users] (
            [User_ID]
      )
</cfquery>

<Cfquery name="Create_FK_CM_Gallery_Items_CM_Galleries" datasource="#Request.Datasource#">
ALTER TABLE [CM_Gallery_Items] ADD
      CONSTRAINT [FK_CM_Gallery_Items_CM_Galleries] FOREIGN KEY
      (
            [Gallery_ID]
      ) REFERENCES [CM_Galleries] (
            [Gallery_ID]
      )
</cfquery>

<Cfquery name="Create_FK_CM_PAGE_CONTENT_CM_Galleries" datasource="#Request.Datasource#">
ALTER TABLE [CM_PAGE_CONTENT] ADD
      CONSTRAINT [FK_CM_PAGE_CONTENT_CM_Galleries] FOREIGN KEY
      (
            [Gallery_ID]
      ) REFERENCES [CM_Galleries] (
            [Gallery_ID]
      ),
      CONSTRAINT [FK_CM_PAGE_CONTENT_CM_Sections] FOREIGN KEY
      (
            [Section_ID]
      ) REFERENCES [CM_Sections] (
            [Section_ID]
      )
</cfquery>

----------------------------------------------------------
Thanks,
Scott
0
Comment
Question by:Scott Bennett
  • 3
  • 3
6 Comments
 
LVL 5

Accepted Solution

by:
spcmnspff earned 200 total points
ID: 7093647
The only Issue I see is your collate statements - won't work in DB2.  However, you can script these tables from SQL server without the collation.  Just make sure the "only script 7.0 compatible features" box is checked in the options from the script objects form, along with the script keys and contraints. etc . . .

You should be able to run that script on db2 . . .

0
 
LVL 14

Author Comment

by:Scott Bennett
ID: 7093677
ok I have removed the Collate statements. I am going to run this script at a clients location tommorrow so i won't be able to test it untill then. Please let me know if there is anything else you guys see. I have heard from someone else that I might have trouble with the long tablenames because the db2 database is on an AS/400 machine. I am going to be connecting to it from a windowsNT machine. do you have any input on that?

Thanks,
Scott

0
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7093695
Actually if your using ODBC to connect from a different machine and running the script in Cold Fusion . . . things should be a little easier.  The ODBC driver should take care of things like filenames, etc behind the scenes.  Let me know how it goes . . .
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 14

Author Comment

by:Scott Bennett
ID: 7093790
ok thanks, I'll get back to you by friday
0
 
LVL 14

Author Comment

by:Scott Bennett
ID: 7121061
Thanks it worked fine (once I got all my unexpected problems got worked out and I could finally try it). sorry it took so long..
0
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7125448
No worries Scott, glad things worked out! . . .
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question