Solved

Connecting to DB2 on AS400 (CFMX)

Posted on 2002-06-17
6
628 Views
Last Modified: 2013-12-24
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
0
Comment
Question by:Scott Bennett
  • 3
  • 2
6 Comments
 
LVL 14

Author Comment

by:Scott Bennett
ID: 7084155
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
 
LVL 5

Accepted Solution

by:
Yog earned 200 total points
ID: 7089270
0
 
LVL 1

Expert Comment

by:g118481
ID: 7093312
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
Save on storage to protect fatherhood memories

You're the dad who has everything. This Father's Day, make sure your family memories are protected. My Passport Ultra has automatic backup and password protection to keep your cherished photos and videos safe. With up to 3TB, you have plenty of room to hold the adventures ahead.

 
LVL 14

Author Comment

by:Scott Bennett
ID: 7093380
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
 
LVL 5

Expert Comment

by:Yog
ID: 7121760
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
 
LVL 5

Expert Comment

by:Yog
ID: 7122934
here is some introduction to db2
http://www.monitor.nl/cbt/db2prog.htm
0

Featured Post

Give your grad a cloud of their own!

With up to 8TB of storage, give your favorite graduate their own personal cloud to centralize all their photos, videos and music in one safe place. They can save, sync and share all their stuff, and automatic photo backup helps free up space on their smartphone and tablet.

Question has a verified solution.

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

Now that Expression Web 4.0 (http://www.microsoft.com/expression/products/Upgrade.aspx) is free if you buy or have the full version of Expression Web 3.0, now is the best time to  migrate from FrontPage to Expression Web (http://www.frontpage-to-exp…
This guide will walk you through the essential considerations and tech stack for building scalable websites. Know how to grow your business the smart way!
The purpose of this video is to demonstrate how to prevent comment spam on a WordPress Website. This will be demonstrated using a Windows 8 PC. Plugin Akismet will be used. Go to your WordPress login page. This will look like the following: myw…
The purpose of this video is to demonstrate how to set up basic WordPress SEO. This will be demonstrated using a Windows 8 PC. The plugin used will be WordPress SEO by Yoast. Go to your WordPress login page. This will look like the following: myw…

867 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now