Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

SQL To Db2 conversion

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
praveenedward_2009

New User


Joined: 26 Aug 2009
Posts: 21
Location: Chennai

PostPosted: Fri Mar 26, 2010 1:03 pm    Post subject: SQL To Db2 conversion
Reply with quote

Hi,

The below mentioned commands are for Sql server 2005.

Code:
CREATE TABLE [dbo].[ADDRESS](
[ADDR-WHO] [varchar](18) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ADDRTYPE-WHO] [varchar](22) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ADDRESS1] [varchar](36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
PRIMARY KEY CLUSTERED
(
 [ADDR-WHO] ASC,
 [ADDRTYPE-WHO] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]



I need to create equivalanet statement in Db2 , i checked for the syntac for Primary Key clustered but not sure the equivalant of COLLATE SQL_Latin1_General_CP1_CI to be given in Db2.

Thanks.
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Mar 26, 2010 7:28 pm    Post subject:
Reply with quote

Hello,

COLLATE SQL_Latin1_General_CP1_CI - do all of your tables have this specification?

If yes, try leaving it out and see what happens.
Back to top
View user's profile Send private message
praveenedward_2009

New User


Joined: 26 Aug 2009
Posts: 21
Location: Chennai

PostPosted: Fri Mar 26, 2010 8:18 pm    Post subject:
Reply with quote

Hi ,

Yes I do have " COLLATE SQL_Latin1_General_CP1_CI " in all of the tables , let me try out leaving it and creating those tables in Db2.

Thanks for the response will revert you back on the same.
Back to top
View user's profile Send private message
praveenedward_2009

New User


Joined: 26 Aug 2009
Posts: 21
Location: Chennai

PostPosted: Fri Mar 26, 2010 9:09 pm    Post subject:
Reply with quote

I Did try out creating the tables need to check for the keyword " PRIMARY KEY CLUSTERED "

(
[ACCT-ID#] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


My answer - Primary key needs to be defined on Db2 on the field ( ACCT_ID ) .. CLUSTERED - Means whenever we create a primary key it creates a default unique index which will be clustered in this case becuase the primary key is the ideal column for a clustered index.

So we need to create a clustered index with the primary key on Db2.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Mar 26, 2010 9:58 pm    Post subject:
Reply with quote

Hello,

Keep in mind that not every table needs to be clustered. . .

Suggest there be some review of the documentation and then discussion. At the top of the page is a link to "IBM Manuals" and there are several publications for multiple versions of db2.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts File Aid to File Manager conversion murali3955 IBM Tools 4 Thu Nov 24, 2016 3:41 pm
No new posts Conversion of a VB file to FB. isssal DFSORT/ICETOOL 1 Mon Oct 31, 2016 10:23 pm
No new posts EBCDIC to ASCII conversion using OCOPY mistah kurtz All Other Mainframe Topics 5 Tue Sep 06, 2016 2:26 pm
No new posts Comparex utility to Syncsort conversion mistah kurtz SYNCSORT 11 Fri Jul 22, 2016 1:53 pm
No new posts changing z/os unicode conversion service meykh2014 All Other Mainframe Topics 0 Tue Jul 12, 2016 10:49 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us