RE: Writing from ms SQL to IBM i database (Darryl Freinkel) -- MIDRANGE-L

Commitment control is always a good thing. IMHO journaling should be turned on for most production items. If not I know you're not doing commitment control, and that is a bad thing. If you fire off a 10 step process and step #9 fails, don't you want all your updates to data from steps 1 through 8 to roll back in order to not leave your data in shambles? I would think so.

-----Original Message-----
From: Darryl Freinkel [mailto:dhfreinkel@xxxxxxxxx]
Sent: Monday, August 17, 2015 3:52 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: Writing from ms SQL to IBM i database (Darryl Freinkel)

I am using SQL 2012.

The solution was to use turn on journaling. It's an unnecessary overhead for this purpose, but the only option. MS needs to be able to roll back it appears.

However, I will try your options below.

Thank you.
Darryl.

Sent from my iPad

On Aug 13, 2015, at 1:09 AM, Gad Miron <gadmiron@xxxxxxxxx> wrote:

Darryl

1. What SQL server version do you use?
2. The following settings work fine for me on SQL Server 2008 R2 a.
Provider: IBM DB2 for i IBMDASQL OLE DB Provider b. Server options:
Collation Compatible False
Data Access True
RPC True
RPC Out True
Use Remote collation True
Collation Name
Connection Timeout 0
Query Timeout 0
Distributor False
Publisher False
Subscriber False
Lazy Schema Validation False
Enable Promotion of Distributed Tra False

I believe the important settings are RPC and RPC Out

The code looks like

insert into as400_prod.proddb.MMSLIBD.BOAK01P

select [trans_no],[trans_internal_no],[trans_creation_date],

[trans_creation_time],[trans_cnfrm_date],[trans_confirm_time],

[trans_code],[trans_error_code],[trans_mont_acc],[trans_acc],

[trans_bo_cust_no],[trans_bo_inv_no],

Case when len([trans_line_no]) < 3 then '0'+[trans_line_no] else
[trans_line_no] end,

[free_fld],

[trans_credit_flag],[trans_no_id_tagmul],[trans_src],[trans_start_mm],

[trans_end_mm],[trans_yyyy],[trans_discount_code],[trans_discount_amnt
],

[trans_base_dbt_amnt],Ltrim(Rtrim([trans_rem1])),Ltrim(Rtrim([trans_re
m2])),

' ', ' ',

[trans_monit_emp],

' ', ' ', ' '

from localdb1.dbo.Bo2monit a

where trans_cnfrm_date = '00000000'

HTH
Gad

date: Tue, 11 Aug 2015 09:56:59 -0400
from: Darryl Freinkel <dhfreinkel@xxxxxxxxx>
subject: Re: Writing from ms SQL to IBM i database

Insert into [INDY2].[C205105V].[VD_GPL].[AX__METRICS]
Values(Current_timestamp, 'Extract', 'STD COST', 'Number of STD
COST records extracted',
(select count(*) from dbo.DMFINVENTJOURNALENTITY ) ) ; Insert
into [INDY2].[C205105V].[VD_GPL].[AX__METRICS]
Values(Current_timestamp,
'Extract',
'OSV',
'Duplicates found',
(SELECT COUNT(*) FROM ( select count(*) from
dbo.DMFINVENTJOURNALENTITY group by inventlocationID, wmslocationid,
ITEMID, CONFIGID having count(*) > 1 )) )
;
Msg 7306, Level 16, State 2, Line 2
Cannot open the table ""C205105V"."VD_GPL"."AX__METRICS"" from OLE DB
provider "DB2OLEDB" for linked server "INDY2". Unknown provider error.

Here are the 2 statements.

Sent from my iPad

On Aug 11, 2015, at 9:37 AM, Charles Wilt <charles.wilt@xxxxxxxxx>

wrote:

Can you post the statement?

On Tue, Aug 11, 2015 at 9:33 AM, Darryl Freinkel
<dhfreinkel@xxxxxxxxx>
wrote:

I need to write some data from a Microsoft database to the IBM i

database.

The problem I have is I have a linked server that I can read from
all

day.

However, I cannot write back to the linked server which is my IBM
i. The script is basically rejecting the 4 part file name, that is,
[linked

server

name].[catalog name].[library name].[file name]

Has anyone experienced this issue and how did you work around the issue?

TIA.

Darryl Freinkel.

Sent from my iPad
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please
take a moment to review the archives at
http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please
take a moment to review the archives at
http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.