Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL compatibility with DB2

Posted on 2002-06-19
6
Medium Priority
?
622 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 800 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this article, we’ll look at how to deploy ProxySQL.
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…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

810 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