AS/400 DB2 TO SQL SERVER

I have created a database in AS/400 DB2 with 250 tables and
23GB data in the database, now I want to shift from DB2 to SQL Server.
How can import the database from AS/400 DB2 to SQL Server, without
corrupting any of the constraints and data. Can anyone explain in
detail. Data is very critical.
ramkumar.nv@.gmail.com wrote:
> I have created a database in AS/400 DB2 with 250 tables and
> 23GB data in the database, now I want to shift from DB2 to SQL Server.
> How can import the database from AS/400 DB2 to SQL Server, without
> corrupting any of the constraints and data. Can anyone explain in
> detail. Data is very critical.
>
I'm not sure that you can get the constraints carried over, so that
might be manual work.
For the data, you can set up a linked server and then script the data
over or you can use DTS/Integration services.
Regards
Steen
|||With db2lookup and db2move commands.I can create the script and ixf
files. Then should I run script for 250 tables manually.
|||So if i create the tables manually by running the script then how to
port the data into the tables from ixf files. How to use the DTS
utility in SQL Server.
|||ramkumar wrote:
> So if i create the tables manually by running the script then how to
> port the data into the tables from ixf files. How to use the DTS
> utility in SQL Server.
>
I know very little about the ixf format/files, so maybe somebody else
can add something on that part?
If you want to read about the DTS utility, you can look it up in Books
On Line. Another option is simply to use SELECT...INSERT to insert all
your data.
No matter how you do it, I think it will require quite a bit of work
to get every imported correctly...:-(.
Regards
Steen
|||Thanq Steen for your response
|||Ramkumar,
how did you create 250 tables? You must have had them scripted, right? If
not, remember, if any object is not in script, it's nowhere. Or script all
your tables on as/400 into script files. remember, as/400 is sql database
and one can derive sql compliant script out of it (not an expert here).
Adjust your scripts to sql server format and recreate them.
Define linked server against your as/4000, use information_schema and
regular character concatenation to create insert into sqltable ...select
from linkedserver.catalog.schema.table.
this should do it.
thanks
farmer
<ramkumar.nv@.gmail.com> wrote in message
news:1143783206.245490.202040@.e56g2000cwe.googlegr oups.com...
> I have created a database in AS/400 DB2 with 250 tables and
> 23GB data in the database, now I want to shift from DB2 to SQL Server.
> How can import the database from AS/400 DB2 to SQL Server, without
> corrupting any of the constraints and data. Can anyone explain in
> detail. Data is very critical.
>

AS/400 DB2 TO SQL SERVER

I have created a database in AS/400 DB2 with 250 tables and
23GB data in the database, now I want to shift from DB2 to SQL Server.
How can import the database from AS/400 DB2 to SQL Server, without
corrupting any of the constraints and data. Can anyone explain in
detail. Data is very critical.ramkumar.nv@.gmail.com wrote:
> I have created a database in AS/400 DB2 with 250 tables and
> 23GB data in the database, now I want to shift from DB2 to SQL Server.
> How can import the database from AS/400 DB2 to SQL Server, without
> corrupting any of the constraints and data. Can anyone explain in
> detail. Data is very critical.
>
I'm not sure that you can get the constraints carried over, so that
might be manual work.
For the data, you can set up a linked server and then script the data
over or you can use DTS/Integration services.
Regards
Steen|||With db2lookup and db2move commands.I can create the script and ixf
files. Then should I run script for 250 tables manually.|||So if i create the tables manually by running the script then how to
port the data into the tables from ixf files. How to use the DTS
utility in SQL Server.|||ramkumar wrote:
> So if i create the tables manually by running the script then how to
> port the data into the tables from ixf files. How to use the DTS
> utility in SQL Server.
>
I know very little about the ixf format/files, so maybe somebody else
can add something on that part?
If you want to read about the DTS utility, you can look it up in Books
On Line. Another option is simply to use SELECT...INSERT to insert all
your data.
No matter how you do it, I think it will require quite a bit of work
to get every imported correctly...:-(.
Regards
Steen|||Thanq Steen for your response|||Ramkumar,
how did you create 250 tables? You must have had them scripted, right? If
not, remember, if any object is not in script, it's nowhere. Or script all
your tables on as/400 into script files. remember, as/400 is sql database
and one can derive sql compliant script out of it (not an expert here).
Adjust your scripts to sql server format and recreate them.
Define linked server against your as/4000, use information_schema and
regular character concatenation to create insert into sqltable ...select
from linkedserver.catalog.schema.table.
this should do it.
thanks
farmer
<ramkumar.nv@.gmail.com> wrote in message
news:1143783206.245490.202040@.e56g2000cwe.googlegroups.com...
> I have created a database in AS/400 DB2 with 250 tables and
> 23GB data in the database, now I want to shift from DB2 to SQL Server.
> How can import the database from AS/400 DB2 to SQL Server, without
> corrupting any of the constraints and data. Can anyone explain in
> detail. Data is very critical.
>

AS/400 DB2 TO SQL SERVER

I have created a database in AS/400 DB2 with 250 tables and
23GB data in the database, now I want to shift from DB2 to SQL Server.
How can import the database from AS/400 DB2 to SQL Server, without
corrupting any of the constraints and data. Can anyone explain in
detail. Data is very critical.ramkumar.nv@.gmail.com wrote:
> I have created a database in AS/400 DB2 with 250 tables and
> 23GB data in the database, now I want to shift from DB2 to SQL Server.
> How can import the database from AS/400 DB2 to SQL Server, without
> corrupting any of the constraints and data. Can anyone explain in
> detail. Data is very critical.
>
I'm not sure that you can get the constraints carried over, so that
might be manual work.
For the data, you can set up a linked server and then script the data
over or you can use DTS/Integration services.
Regards
Steen|||With db2lookup and db2move commands.I can create the script and ixf
files. Then should I run script for 250 tables manually.|||So if i create the tables manually by running the script then how to
port the data into the tables from ixf files. How to use the DTS
utility in SQL Server.|||ramkumar wrote:
> So if i create the tables manually by running the script then how to
> port the data into the tables from ixf files. How to use the DTS
> utility in SQL Server.
>
I know very little about the ixf format/files, so maybe somebody else
can add something on that part?
If you want to read about the DTS utility, you can look it up in Books
On Line. Another option is simply to use SELECT...INSERT to insert all
your data.
No matter how you do it, I think it will require quite a bit of work
to get every imported correctly...:-(.
Regards
Steen|||Thanq Steen for your response|||Ramkumar,
how did you create 250 tables? You must have had them scripted, right? If
not, remember, if any object is not in script, it's nowhere. Or script all
your tables on as/400 into script files. remember, as/400 is sql database
and one can derive sql compliant script out of it (not an expert here).
Adjust your scripts to sql server format and recreate them.
Define linked server against your as/4000, use information_schema and
regular character concatenation to create insert into sqltable ...select
from linkedserver.catalog.schema.table.
this should do it.
thanks
farmer
<ramkumar.nv@.gmail.com> wrote in message
news:1143783206.245490.202040@.e56g2000cwe.googlegroups.com...
> I have created a database in AS/400 DB2 with 250 tables and
> 23GB data in the database, now I want to shift from DB2 to SQL Server.
> How can import the database from AS/400 DB2 to SQL Server, without
> corrupting any of the constraints and data. Can anyone explain in
> detail. Data is very critical.
>

Array as procedure parameter

I have a shift definition table with the columns:

shift_id: shift's id

shift_name: shift's name

shift_number_of_day: shift's "position" on the day

initial_hour: shift's initial hour

final_hour: shift's final hour

The shift definition depends on the company: company A may have 2 shifts, and company B may have 3 shifts, for example.
I need to load a dimension table, dim_time, that should have a row for each hour of each day of a specific year. I would have

alternate_time_key ... shift_name ...
1/1/2006 01:00:00 GraveYard
1/1/2006 02:00:00 GraveYard

and so on, until it reaches the end of the year.

So in my procedure to load the dimension table, I would have something like

IF (@.alternateTimeKeyHour >= @.paramShift1InitialHour) AND (@.alternateTimeKeytHour <= @.paramShift1FinalHour)
BEGIN
SET @.shiftName = @.paramFirstShiftName;
SET @.shiftNumberOfDay = 1;
END
ELSE IF (@.alternateTimeKeyHour >= @.paramShift2InitialHour) AND (@.alternateTimeKeyHour <= @.paramShift2FinalHour)
BEGIN
SET @.shiftName = @.paramSecondShiftName;
SET @.shiftNumberOfDay = 2;
END
.
.
.

The problem is that I would have a variable number of shifts (variable number of parameters!)...
The only solution I could think was using an array, but as far as I could see it's not possible to pass
an array as a parameter to a procedure. Is this right? Is there a better solution to do this? Can anyone help me please?

Thank you!

Personally, I always use XML for this type of thing. You can then use OPENXML in the stored procedure to turn the xml into a table that you can join to.

Here is a great article about your choices for passing an array to a stored procedure.

http://www.sommarskog.se/arrays-in-sql.html

|||Thank you Ryan!