Solved

SQL compatibility with DB2

Posted on 2002-06-19
6
585 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
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…
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…

806 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