Connecting to DB2 on AS400 (CFMX)

I am going to be connecting ColdFusion MX proffessional on and NT server to a DB2 database on an AS400 machine. Are there any hurdles that I should be aware of before I start?

Thanks,
Scott
LVL 14
Scott BennettManager TechnologyAsked:
Who is Participating?
 
Scott BennettManager TechnologyAuthor Commented:
First sentace should read:

I am going to be connecting ColdFusion MX proffessional on a Windows NT server to a DB2 database on an AS400
machine
0
 
g118481Commented:
sbennett,

I have been using DB2 CONNECT to access DB2 tables on a Mainframe OS/390 for about 6 months, and it works fine.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Scott BennettManager TechnologyAuthor Commented:
I have been told that there may be some restrictions in regards to the amount of charachters that I can use when I assign names of table columns.

can you please tell me if the following SQL statements are compatible with DB2? I originally created them for a SQL Server 2000 Database but I need to create an identical data structure on the DB2 database and need to know if there are any syntax changes I need to make. (This is a little more then I originally asked for so I am increasing the points to 200)

-------------------------------------------------------
<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>
<cfoutput>DONE!</cfoutput>

0
 
YogCommented:
bennet,

i dont deserve the points , i was just searching and i got the url. i have worked on mainframe db2 4 yrs back (not as400) and am sure if you use embedded sql its gonna be faster than odbc, i am not really sure wheather the odbc for db2 on as400 is gonna be fast.

yog
0
 
YogCommented:
here is some introduction to db2
http://www.monitor.nl/cbt/db2prog.htm
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.