Converting DB2 Timestamp Data to SQL Server 2005 - Problems with Unique Index

I am attempting to move a timestamp data column from DB2 to SQL Server 2005. Normally not a big deal but the column is part of unique index.

The DB2 timestamp has seconds of ss.ssssss but SQL Server only has ss.sss.

Most all the times entered into this column are a from an automated process so they are really close together timewise.

Here is what I have come up with so far:

1. Fast Load OLEDB with a batch of 10,000 records at a time

2. On the fail of the batch redirect rows to a regular table load OLEDB insert task

3. On the fail of the single insert redirect rows to script that ups the seconds one tick.

4. Attempt one last insert of the modified rows

5. If fail, then store the record off to a delimited text file

I am hoping to get the number of records that wind up in the delimited text file to be a very small number and not in the 1,000+.

Any help would be appreciated.

Redirecting errors from the FastLoad to a regular OLEDB destination is a technique that I've used before to capture only the row that caused the error - that should work fine.

I have another question, though. If you can change this timestamp value (as you are planning in step 3), it doesn't seem to be all that significant. Why not drop just add an identity column to the unique index?

|||

Adding an Identity column to the table and then adding into the index sounds like a good idea to get around the problem. Especially since the package is going to be scheduled to run every 15 to 30 minutes to sync the DB2 table to the SQL Server table during the migration time frame.

Thanks for the suggestion. I'll let you know what we have decided to do.

|||

Upon further inspection of this process and doing a small test this method is not an option for me.

The addition of an indentity column to the unqiue index would now in essence make the index non-unique. The identity column is a unique value and would then allow for the business rule of this date time column to be broken.

I am attempting to try and see if I can capture the data column as a string for manipulation, but right now the OleDB connection is pulling the catalog info from DB2 and knows that the column is supposed to be a date time. Any attempt to use the column in the SSIS package as a character I get errors.

Still looking for suggestions before starting down the path of writing an application or off the shelf tool to handle the sync between these tables.

Thanks.

|||

You should be able to use a derived column to convert the value to a string - that is probably your best bet to avoid violating the business rule.

Code Snippet

(DT_WSTR, 10) [ColumnName]

Converting a Clustered Index on a PK Identity field to non-clustered

Hi there, I have a table that has an IDENTITY column and it is the PK of this table. By default SQL Server creates a unique clustered index on the PK, but this isn't what I wanted. I want to make a regular unique index on the column so I can make a clustered index on a different column.

If I try to uncheck the Clustered index option in EM I get a dialog that says "Cannot convert a clustered index to a nonclustered index using the DROP_EXISTING option.". If I simply try to delete the index I get the following "An explicit DROP INDEX is not allowed on index 'index name'. It is being used for PRIMARY KEY constraint enforcement.

So do I have to drop the PK constraint now? How does that affect all the tables that have FK relationships to this table?

ThanksYou can certainly drop the PK and recreate it non-clustered.

If you have FKs to this PK, you'll have to drop them first though, and then recreate them once you have recreated your PK.

This is one reason why using the UI for table design is not a good idea - better to write the DDL yourself so you know exactly what you are getting. :}|||Here are a couple of scripts to help you identify FKs related to the PK column that you have.

Instructions:
1. Make a backup of the DB before you do this.
2. Copy and paste the two snippets of code into QA.
3. Press CTRL+Shift+M to set the variables for the table name and the column ID of the column with the PK.
4. Run the CREATE script FIRST.
5. Copy and paste the results into another QA window.
6. Run the DROP script.
7. Copy and paste the drop script results into a QA window
8. Execute the drop statement
9. Alter your PK
10. Execute the CREATE script that was generated earlier.

/* DROP SPECIFIC FOREIGN KEYS */
print '-- Drop Specific Foreign Keys related to a field'
print ''

DECLARE @.fkName varchar(800), @.tabName varchar(800), @.owner varchar(800)
DECLARE @.pline varchar(8000)

DECLARE fkCursor CURSOR FOR
select distinct object_name(constid) FK_Name, object_name(fkeyid) as Local_Tab_Name, user_name(so.uid) as Local_Tab_Owner
from sysforeignkeys k inner join sysobjects so on
k.fkeyid = so.id
where k.rkeyid = object_id('<table_name, varchar(255), MyTable>')
and k.rkey = <col_id, int, 1>
order by object_name(fkeyid)

OPEN fkCursor

FETCH NEXT FROM fkCursor
INTO @.fkName, @.tabName, @.owner

WHILE @.@.FETCH_STATUS = 0
BEGIN

select @.pline = 'ALTER TABLE [' + @.owner + '].[' + @.tabName + '] '

select @.pline = @.pline + 'DROP CONSTRAINT [' + @.fkName + ']' +
CHAR(13) + CHAR(10) + 'go'
print @.pline

FETCH NEXT FROM fkCursor
INTO @.fkName, @.tabName, @.owner
END

CLOSE fkCursor
DEALLOCATE fkCursor
GO

Code to generate DDL for FKs

-- Generate Adds for SELECTED Foreign Keys in Database
print '-- Add Foreign Keys'
print ''

DECLARE @.fkName varchar(800), @.tabName varchar(800), @.refName varchar(800), @.fkOwner varchar(800), @.refOwner varchar(800)
DECLARE @.isDel int, @.isUpd int, @.isNotRepl int, @.isNotTrusted int, @.isDisabled int, @.fkCol varchar(8000), @.refCol varchar(8000)
DECLARE @.pline varchar(8000)

DECLARE fkCursor CURSOR FOR
select distinct object_name(constid) FK_Name, object_name(fkeyid) as Local_Tab_Name,
object_name(rkeyid) as Remote_Tab_Name,
OBJECTPROPERTY ( constid , 'CnstIsDeleteCascade' ) DeleteCascade,
OBJECTPROPERTY ( constid , 'CnstIsUpdateCascade' ) UpdateCascade,
OBJECTPROPERTY ( constid , 'CnstIsNotRepl' ) NotForReplication,
OBJECTPROPERTY ( constid , 'CnstIsNotTrusted' ) NotTrusted,
OBJECTPROPERTY ( constid , 'CnstIsDisabled' ) Disabled,
USER_NAME(fkso.uid) fkOwner,
USER_NAME(refso.uid) refOwner
from
sysforeignkeys k inner join sysobjects fkso on
k.fkeyid = fkso.id
inner join sysobjects refso on
k.rkeyid = refso.id
where
k.rkeyid = object_id('<table_name, varchar(255), MyTable>')
and k.rkey = <col_id, int, 1>
order by object_name(fkeyid)

OPEN fkCursor

FETCH NEXT FROM fkCursor
INTO @.fkName, @.tabName, @.refName, @.isDel, @.isUpd, @.isNotRepl, @.isNotTrusted, @.isDisabled, @.fkOwner, @.refOwner

WHILE @.@.FETCH_STATUS = 0
BEGIN
select @.fkCol = NULL
SELECT @.fkCol = ISNULL(@.fkCol + ', ','') + '[' + col_name(fkeyid, fkey) + ']'
from sysforeignkeys
where object_name(constid) = @.fkName
order by keyno

select @.refCol = NULL
SELECT @.refCol = ISNULL(@.refCol + ', ','') + '[' + col_name(rkeyid, rkey) + ']'
from sysforeignkeys
where object_name(constid) = @.fkName
order by keyno
select @.pline = 'ALTER TABLE [' + @.fkOwner + '].[' + @.tabName + '] '

if @.isNotTrusted = 1
select @.pline = @.pline + 'WITH NOCHECK'
select @.pline = @.pline + ' ADD CONSTRAINT [' + @.fkName + ']' + CHAR(13) + CHAR(10) +
' FOREIGN KEY (' + @.fkCol + ') REFERENCES [' + @.refOwner + '].[' + @.refName +
'] (' + @.refCol + ')'

if @.isDel = 1
select @.pline = @.pline + CHAR(13) + CHAR(10) +
' ON DELETE CASCADE'
if @.isUpd = 1
select @.pline = @.pline + CHAR(13) + CHAR(10) +
' ON UPDATE CASCADE'
if @.isNotRepl = 1
select @.pline = @.pline + CHAR(13) + CHAR(10) +
' NOT FOR REPLICATION'
select @.pline = @.pline + CHAR(13) + CHAR(10) + 'go'
if @.isDisabled = 1
select @.pline = @.pline + CHAR(13) + CHAR(10) +
'ALTER TABLE [dbo].[' + @.tabName + ']' +
' NOCHECK CONSTRAINT [' + @.fkName + ']' +
CHAR(13) + CHAR(10) + 'go'
print @.pline

FETCH NEXT FROM fkCursor
INTO @.fkName, @.tabName, @.refName, @.isDel, @.isUpd, @.isNotRepl, @.isNotTrusted, @.isDisabled, @.fkOwner, @.refOwner
END

CLOSE fkCursor
DEALLOCATE fkCursor
GO

Regards,

hmscott|||Thanks for the tips. I normally do my DDL with T-SQL and not EM, but this was just some quick and dirty test stuff. I should have known that I would have to drop the FK constraints before dropping the PK constraint.

Convert when executing query

When I am executing a simple select query in a database with correct index set it takes a long time (over 30 secunds) to execute. In another database with an identical table it runs on less then one second.
The differense I can find is that when I check execution plan on the slow database I get the arguments of the index scan to "convert([table].[column])=Convert([@.1]) and i don't get this in the other database. Does anyone have any idea on why?
Best Regards HalTry updating statistics and recreating indexes on your slow database.

blindman|||I have the update statistics automaticly set to true. Should I do that anyway?
//Hal

Originally posted by blindman
Try updating statistics and recreating indexes on your slow database.

blindman

Convert Clustered Index to Non-Clustered

How do I convert an SQL Server 2000 clustered index to non-clustered?
Enterprise Manager would not let me.You drop the clustered index and create a non-clustered index with the same
keys. Note however that the clustered index IS the table and that if you
drop the clustered index you will be left with a heap (unsorted collection
of pages) for the table and that the non-clustered index will just contain
the keys and pointers to the actual data pages.
HTH
Jerry
"jmelkerson" <jmelkerson@.discussions.microsoft.com> wrote in message
news:9D468AD8-7F08-426A-8745-F93CF9756F60@.microsoft.com...
> How do I convert an SQL Server 2000 clustered index to non-clustered?
> Enterprise Manager would not let me.|||Here is why asked the question to begin with:
I have a table with almost 2 million records. The primary is is also the
clustered index. Records are added all day, every day. There is no order to
the way in which new records "arrive". New records/primary keys are not
presented in ascending order therefore a new record could be required to be
inserted anywhere within the table. Sometimes when the system slows down I
can't find any assignable cause; no locks, blocks, etc. I am thinking that
maybe SQL Server is taking a long time keeping the clustered index in order.
Maybe if I make the index non-clustered things will speed up. Does this make
sense?
"Jerry Spivey" wrote:
> You drop the clustered index and create a non-clustered index with the same
> keys. Note however that the clustered index IS the table and that if you
> drop the clustered index you will be left with a heap (unsorted collection
> of pages) for the table and that the non-clustered index will just contain
> the keys and pointers to the actual data pages.
> HTH
> Jerry
> "jmelkerson" <jmelkerson@.discussions.microsoft.com> wrote in message
> news:9D468AD8-7F08-426A-8745-F93CF9756F60@.microsoft.com...
> > How do I convert an SQL Server 2000 clustered index to non-clustered?
> > Enterprise Manager would not let me.
>
>|||Is there an auto incrementing key on the table i.e., an IDENITY property on
a column? If so that may be a good possible candidate for the clustered
index as the data page for the next insert is likely to already be in the
cache. If not you could add one. You could also try dropping the clustered
index trying it without the NC index, then add the NC index to see which is
faster. Additional NC indexes generally degrade write performance.
HTH
Jerry
"jmelkerson" <jmelkerson@.discussions.microsoft.com> wrote in message
news:40BDA1A0-1C45-4DDA-A007-23E4AB6228EC@.microsoft.com...
> Here is why asked the question to begin with:
> I have a table with almost 2 million records. The primary is is also the
> clustered index. Records are added all day, every day. There is no order
> to
> the way in which new records "arrive". New records/primary keys are not
> presented in ascending order therefore a new record could be required to
> be
> inserted anywhere within the table. Sometimes when the system slows down
> I
> can't find any assignable cause; no locks, blocks, etc. I am thinking
> that
> maybe SQL Server is taking a long time keeping the clustered index in
> order.
> Maybe if I make the index non-clustered things will speed up. Does this
> make
> sense?
> "Jerry Spivey" wrote:
>> You drop the clustered index and create a non-clustered index with the
>> same
>> keys. Note however that the clustered index IS the table and that if you
>> drop the clustered index you will be left with a heap (unsorted
>> collection
>> of pages) for the table and that the non-clustered index will just
>> contain
>> the keys and pointers to the actual data pages.
>> HTH
>> Jerry
>> "jmelkerson" <jmelkerson@.discussions.microsoft.com> wrote in message
>> news:9D468AD8-7F08-426A-8745-F93CF9756F60@.microsoft.com...
>> > How do I convert an SQL Server 2000 clustered index to non-clustered?
>> > Enterprise Manager would not let me.
>>|||Yes, there is an Identity column named [Index]. Is an Identity column by
default indexed? The column that is the primary key needs to remain so.
Are you suggesting that I make the primary key a non-clustered index and make
the [Index] column a clustered index?
"Jerry Spivey" wrote:
> Is there an auto incrementing key on the table i.e., an IDENITY property on
> a column? If so that may be a good possible candidate for the clustered
> index as the data page for the next insert is likely to already be in the
> cache. If not you could add one. You could also try dropping the clustered
> index trying it without the NC index, then add the NC index to see which is
> faster. Additional NC indexes generally degrade write performance.
> HTH
> Jerry
> "jmelkerson" <jmelkerson@.discussions.microsoft.com> wrote in message
> news:40BDA1A0-1C45-4DDA-A007-23E4AB6228EC@.microsoft.com...
> > Here is why asked the question to begin with:
> > I have a table with almost 2 million records. The primary is is also the
> > clustered index. Records are added all day, every day. There is no order
> > to
> > the way in which new records "arrive". New records/primary keys are not
> > presented in ascending order therefore a new record could be required to
> > be
> > inserted anywhere within the table. Sometimes when the system slows down
> > I
> > can't find any assignable cause; no locks, blocks, etc. I am thinking
> > that
> > maybe SQL Server is taking a long time keeping the clustered index in
> > order.
> > Maybe if I make the index non-clustered things will speed up. Does this
> > make
> > sense?
> >
> > "Jerry Spivey" wrote:
> >
> >> You drop the clustered index and create a non-clustered index with the
> >> same
> >> keys. Note however that the clustered index IS the table and that if you
> >> drop the clustered index you will be left with a heap (unsorted
> >> collection
> >> of pages) for the table and that the non-clustered index will just
> >> contain
> >> the keys and pointers to the actual data pages.
> >>
> >> HTH
> >>
> >> Jerry
> >> "jmelkerson" <jmelkerson@.discussions.microsoft.com> wrote in message
> >> news:9D468AD8-7F08-426A-8745-F93CF9756F60@.microsoft.com...
> >> > How do I convert an SQL Server 2000 clustered index to non-clustered?
> >> > Enterprise Manager would not let me.
> >>
> >>
> >>
>
>|||Hmmm...not sure I would have named the column 'index' as that is a SQL
Server reserved word. No, a column with an IDENTITY property is not indexed
by default. It is an option as the PRIMARY KEY column is not "required" to
be a clustered index and a clustered index may be better suited to the
IDENTITY column in this case. I would try a few indexing strategies and
choose the best one in performance from your tests.
HTH
Jerry
"jmelkerson" <jmelkerson@.discussions.microsoft.com> wrote in message
news:A6EBD861-9E73-4896-8AFB-2AE09CC177FB@.microsoft.com...
> Yes, there is an Identity column named [Index]. Is an Identity column by
> default indexed? The column that is the primary key needs to remain so.
> Are you suggesting that I make the primary key a non-clustered index and
> make
> the [Index] column a clustered index?
> "Jerry Spivey" wrote:
>> Is there an auto incrementing key on the table i.e., an IDENITY property
>> on
>> a column? If so that may be a good possible candidate for the clustered
>> index as the data page for the next insert is likely to already be in the
>> cache. If not you could add one. You could also try dropping the
>> clustered
>> index trying it without the NC index, then add the NC index to see which
>> is
>> faster. Additional NC indexes generally degrade write performance.
>> HTH
>> Jerry
>> "jmelkerson" <jmelkerson@.discussions.microsoft.com> wrote in message
>> news:40BDA1A0-1C45-4DDA-A007-23E4AB6228EC@.microsoft.com...
>> > Here is why asked the question to begin with:
>> > I have a table with almost 2 million records. The primary is is also
>> > the
>> > clustered index. Records are added all day, every day. There is no
>> > order
>> > to
>> > the way in which new records "arrive". New records/primary keys are
>> > not
>> > presented in ascending order therefore a new record could be required
>> > to
>> > be
>> > inserted anywhere within the table. Sometimes when the system slows
>> > down
>> > I
>> > can't find any assignable cause; no locks, blocks, etc. I am thinking
>> > that
>> > maybe SQL Server is taking a long time keeping the clustered index in
>> > order.
>> > Maybe if I make the index non-clustered things will speed up. Does
>> > this
>> > make
>> > sense?
>> >
>> > "Jerry Spivey" wrote:
>> >
>> >> You drop the clustered index and create a non-clustered index with the
>> >> same
>> >> keys. Note however that the clustered index IS the table and that if
>> >> you
>> >> drop the clustered index you will be left with a heap (unsorted
>> >> collection
>> >> of pages) for the table and that the non-clustered index will just
>> >> contain
>> >> the keys and pointers to the actual data pages.
>> >>
>> >> HTH
>> >>
>> >> Jerry
>> >> "jmelkerson" <jmelkerson@.discussions.microsoft.com> wrote in message
>> >> news:9D468AD8-7F08-426A-8745-F93CF9756F60@.microsoft.com...
>> >> > How do I convert an SQL Server 2000 clustered index to
>> >> > non-clustered?
>> >> > Enterprise Manager would not let me.
>> >>
>> >>
>> >>
>>

Convert Clustered Index to Non-Clustered

How do I convert an SQL Server 2000 clustered index to non-clustered?
Enterprise Manager would not let me.You drop the clustered index and create a non-clustered index with the same
keys. Note however that the clustered index IS the table and that if you
drop the clustered index you will be left with a heap (unsorted collection
of pages) for the table and that the non-clustered index will just contain
the keys and pointers to the actual data pages.
HTH
Jerry
"jmelkerson" <jmelkerson@.discussions.microsoft.com> wrote in message
news:9D468AD8-7F08-426A-8745-F93CF9756F60@.microsoft.com...
> How do I convert an SQL Server 2000 clustered index to non-clustered?
> Enterprise Manager would not let me.|||Here is why asked the question to begin with:
I have a table with almost 2 million records. The primary is is also the
clustered index. Records are added all day, every day. There is no order t
o
the way in which new records "arrive". New records/primary keys are not
presented in ascending order therefore a new record could be required to be
inserted anywhere within the table. Sometimes when the system slows down I
can't find any assignable cause; no locks, blocks, etc. I am thinking that
maybe SQL Server is taking a long time keeping the clustered index in order.
Maybe if I make the index non-clustered things will speed up. Does this mak
e
sense?
"Jerry Spivey" wrote:

> You drop the clustered index and create a non-clustered index with the sam
e
> keys. Note however that the clustered index IS the table and that if you
> drop the clustered index you will be left with a heap (unsorted collection
> of pages) for the table and that the non-clustered index will just contain
> the keys and pointers to the actual data pages.
> HTH
> Jerry
> "jmelkerson" <jmelkerson@.discussions.microsoft.com> wrote in message
> news:9D468AD8-7F08-426A-8745-F93CF9756F60@.microsoft.com...
>
>|||Is there an auto incrementing key on the table i.e., an IDENITY property on
a column? If so that may be a good possible candidate for the clustered
index as the data page for the next insert is likely to already be in the
cache. If not you could add one. You could also try dropping the clustered
index trying it without the NC index, then add the NC index to see which is
faster. Additional NC indexes generally degrade write performance.
HTH
Jerry
"jmelkerson" <jmelkerson@.discussions.microsoft.com> wrote in message
news:40BDA1A0-1C45-4DDA-A007-23E4AB6228EC@.microsoft.com...[vbcol=seagreen]
> Here is why asked the question to begin with:
> I have a table with almost 2 million records. The primary is is also the
> clustered index. Records are added all day, every day. There is no order
> to
> the way in which new records "arrive". New records/primary keys are not
> presented in ascending order therefore a new record could be required to
> be
> inserted anywhere within the table. Sometimes when the system slows down
> I
> can't find any assignable cause; no locks, blocks, etc. I am thinking
> that
> maybe SQL Server is taking a long time keeping the clustered index in
> order.
> Maybe if I make the index non-clustered things will speed up. Does this
> make
> sense?
> "Jerry Spivey" wrote:
>|||Yes, there is an Identity column named [Index]. Is an Identity column b
y
default indexed? The column that is the primary key needs to remain so.
Are you suggesting that I make the primary key a non-clustered index and mak
e
the [Index] column a clustered index?
"Jerry Spivey" wrote:

> Is there an auto incrementing key on the table i.e., an IDENITY property o
n
> a column? If so that may be a good possible candidate for the clustered
> index as the data page for the next insert is likely to already be in the
> cache. If not you could add one. You could also try dropping the cluster
ed
> index trying it without the NC index, then add the NC index to see which i
s
> faster. Additional NC indexes generally degrade write performance.
> HTH
> Jerry
> "jmelkerson" <jmelkerson@.discussions.microsoft.com> wrote in message
> news:40BDA1A0-1C45-4DDA-A007-23E4AB6228EC@.microsoft.com...
>
>|||Hmmm...not sure I would have named the column 'index' as that is a SQL
Server reserved word. No, a column with an IDENTITY property is not indexed
by default. It is an option as the PRIMARY KEY column is not "required" to
be a clustered index and a clustered index may be better suited to the
IDENTITY column in this case. I would try a few indexing strategies and
choose the best one in performance from your tests.
HTH
Jerry
"jmelkerson" <jmelkerson@.discussions.microsoft.com> wrote in message
news:A6EBD861-9E73-4896-8AFB-2AE09CC177FB@.microsoft.com...[vbcol=seagreen]
> Yes, there is an Identity column named [Index]. Is an Identity column
by
> default indexed? The column that is the primary key needs to remain so.
> Are you suggesting that I make the primary key a non-clustered index and
> make
> the [Index] column a clustered index?
> "Jerry Spivey" wrote:
>