× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. MIDRANGE-L
  3. August 2015

RE: Commitment Control Was: Writing from ms SQL to IBM i database (Darryl Freinkel)

Not sure what the problem is here. This is human error, not a program error.


-----Original Message-----
From: rob@xxxxxxxxx [mailto:rob@xxxxxxxxx]
Sent: Wednesday, August 19, 2015 10:00 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Commitment Control Was: Writing from ms SQL to IBM i database (Darryl Freinkel)

Matt,

Commitment control, rollback capability, etc all sound good but think of this scenario:

You run a large sql update which posts a transaction file into the item master file. As part of this update it updates the quantity on hand in the item master.

Item Quantity
X 100
becomes
X 200

Now let's say someone is running some inventory transaction and it updates the quantity also
X 205

Can you really notice "Aw shucks, I forgot the WHERE clause on my sql statement. I'll just do a ROLLBACK."? What happens to the quantity of X?
It's not like it changes it from 205 to 105.

I do agree that journalling is important. I argued for it for years. They put it off until we got Mimix. Now they've gone gonzo with journal receivers. Not that Mimix requires such retention. The developers just love the history and tracking so they make sure the retention is long enough to always span two month ends. (77 days or some such thing).


Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: Matt Olson <Matt.Olson@xxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 08/19/2015 10:38 AM
Subject: RE: Writing from ms SQL to IBM i database (Darryl
Freinkel)
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.