migrating data via bcp and sqlcmd with portable subfiles

[Joe Boyle]

And just in case you don’t have PH on the Windows side, I found that the
following works with ‘MSSQLServer 2005 express’ so it should’nt be a problem
to rewrite for ‘MSSQLServer 2005’,

bcp newschema.dbo. bcpin_table _preload in c:\path\feedbcp.ps -t ~ -c -r "|"
-e errout.log -U sa -P mypassword -S mySQLSERVERserver

sqlcmd  -U sa -P mypassword -S mySQLSERVERserver <  feedbcp_script.sql

The feedbcp_script.sql script file content is below,

use newone
go
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
begin tran
update bcpin_table set col7 = (select b.col7 from  bcpin_table_preload b
where bcpin_table.col1 = b.col1 and 
(bcpin_table.col7 is null ) or (bcpin_table.col7 != b.col7) )
go
commit
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
begin tran
delete from bcpin_table_preload where col1 in (select col1 from 
bcpin_table)
go
commit
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
begin tran
insert into bcpin_table select * from bcpin_table_preload
go
commit
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
begin tran
delete from bcpin_table_preload
go
commit
exit

and use quiz code like that below to generate the .ps file ( I tested
something like this in the past and it worked ok then )

can cle
set veri error
set rep nolim

acc orders in axnwind

define d_OrderID char*10 = ascii(OrderID ,10)

define d_Freight char*14 = formatnumber ( Freight/100,
"+000000.0000","-000000.0000","0.00")

define d_date date century included = sysdate

define d_time char*14  =  formatnumber ( d_date*10000 + systime, "00000000
00:00")


define d_sep char*1 = "~"
define d_eol char*1 = "|"

set sub name /path/feedbcp portable

rep summ &
d_OrderID &
d_sep &
d_Freight &
d_sep &
d_freight &
d_sep &
employeeid &
d_sep &
shipvia &
d_sep &
d_date &
d_sep &
d_time &
d_eol

bui feedbcp


________________________________________
From: powerh-l-bounces+atla38=dsl.pipex.com at lists.sowder.com
[mailto:powerh-l-bounces+atla38=dsl.pipex.com at lists.sowder.com] On Behalf Of
Glenn Baxter
Sent: 29 January 2007 23:04
To: Viet Nguyen; powerh-l at lists.sowder.com
Subject: RE: migrating data

Hi Viet, 
As per Joe's suggestion I have had much greater success unloading to
portable subfiles.
 
Perform a Binary transfer of the data portion (.ps) and an ascii transfer of
the dictionary portion (.psd) using wither Reflection etc of Ftp.
Having built a PHD on the "other side" in your case NT Server, simply use
Qtp to reload/load the SQL Server tables.
I had built the SQL tables with underscore "_" element names; replacing "-"
naming, so; edited the dictionary (.psd) files performing a global replace
of "-" to "_".
This removes the need to manually rename elements/columns etc and Qtp then
does automatic initialisation.
Judging from your code below; this may not be necessary.
 
I found this method to be reliable and the performance quite fast.
You have the advantage of being able to transfer in one process and the
reload concurrently from the NT Server in another session.
 
Sincerely
Glenn Baxter
____________
Baxter IT Solutions
Software that works.
456 Victoria Parade 
East Melbourne 
Victoria Australia 3002
p: 03-9415-8733
m: 0412-826-503
f: 03-9415-8911
e: glenn at baxterit.com.au
 
This e-mail message (and attachments) may contain information that is
confidential to Baxter IT Solutions. If you are not the intended recipient
you cannot use, distribute or copy the message or attachments. In such a
case, please notify the sender by return e-mail immediately and erase all
copies of the message and attachments. Opinions, conclusions and other
information in this message and attachments that do not relate to the
official business of Baxter IT Solutions are neither given nor endorsed
by it.

 

________________________________________
From: powerh-l-bounces+glenn=baxterit.com.au at lists.sowder.com
[mailto:powerh-l-bounces+glenn=baxterit.com.au at lists.sowder.com] On Behalf
Of Joe Boyle
Sent: Tuesday, 30 January 2007 3:24 AM
To: 'Viet Nguyen'; powerh-l at lists.sowder.com
Subject: RE: migrating data
I read somewhere that bcp is the fastest way to load into MSSQL, how about
sending a portable subfile over to the Windows box and then use it to supply
bcp.

________________________________________
From: powerh-l-bounces+atla38=dsl.pipex.com at lists.sowder.com
[mailto:powerh-l-bounces+atla38=dsl.pipex.com at lists.sowder.com] On Behalf Of
Viet Nguyen
Sent: 29 January 2007 16:07
To: powerh-l at lists.sowder.com
Subject: migrating data

Hi ,
Would anyone be able to show me how to speed up data exporting from AIX to
MSSQL?
It takes around 24 hours to export the data 
 
Here is my set up:
 
1. Powerhouse with CISAM file on AIX 
2. MSSQL DB is on Windows box   
3. I am using Data Direct ODBC driver to connect to the MSSQL db from the
AIX box and 
4. we’re on a 1gigabit network. 
5. Here is a qtp run sample: 
 
 
set process nolimit
commit at request
set input   nolimit
 
use setlimit
 
request abadj
access abadj alias oldabadj
output abadj in sqldevl add
 
request abadjdesc
access abadjdesc alias oldabadjdesc
output abadjdesc in sqldevl add
 
request abadjdet
access abadjdet alias oldabadjdet
output abadjdet in sqldevl add
 
;request abapprover
;access abapprover alias oldabapprover
;output abapprover in sqldevl add
 
request abbudexp
access abbudexp alias oldabbudexp
output abbudexp in sqldevl add
 
request abbudgroup
access abbudgroup alias oldabbudgroup
output abbudgroup in sqldevl add