Ansicht
Dokumentation
03540 - DB2 Locking
General Data in Customer Master BAL Application Log DocumentationThis documentation is copyright by SAP AG.
DB2 Locking
Mike,At my former employer, they have a Windows NT datawarehouse software
application that uses SQL commands to access AS/400 files using 32bit ODBC
driver. One of the AS/400 files accessed has more than 2 million records and
yet the performance is extremely good on both the Windows NT client and the
AS/400(S30 w/8way processor I can't remember the exact size of memory).
Local and remote dial-up users experienced better response time using the
datawarehouse software compared to using the standard SAP SIS functionality.
You are right about DB2 locking. The datawarehouse user can lock the record
or the entire SAP table(which is also a native AS/400 DB file). To prevent
locking of the SAP tables, we design the interface as follows:
1) Create an AS/400 Library (example: MYLIB).
2) Create an AS/400 file in the non-SAP library (example: MYLIB/MYMARA).
3) Create a user-defined table in SAP that has the same fields(fieldname and
field attributes) as that of the AS/400 file (example: ZMARA).
4) Create an ABAP program to extract records from the standard SAP table
into the user-defined table (example: from MARA into ZMARA).
We did step 2 to step 4 for each SAP table we needed to access.
5) Create an AS/400 CL program to copy records from the SAP user-defined
table to the non-SAP AS/400 file (example: CPYF from R3sidDATA/ZMARA to
MYLIB/MYMARA).
6) After the ABAP extract program is excuted successfully, we run the CL
program created on step 5. We always run the CL when no user is using the
SQL datawarehouse software. In our case, the CPYF command w/parameter
*REPLACE or a CLRPFM command fails when a user is using or forgets to close
the SQL datawarehouse software. The reason is, all the files accessed by the
open datawarehouse software are locked on the AS/400. They remain locked
until the datawarehouse software is closed.
I hope this help you somehow. Send me an email if you have any question.
Good luck!
Willie Lazaro
>From: "Mike Martin, IS, Sousa" <MMartinZa...>
>To: "SAP - AS/400 List (E-mail)" <sap400Zm...>
>Subject: DB2 Locking
>Date: Tue, 16 Oct 2001 11:16:29 -0700
>
>I have a general question for the group. Our data warehouse team is
>recommending that we use ODBC access to read and download SAP production
>table data to an SQL server. My thoughts are that this isn't a very good
>idea, yet I need your help to clarify and define objections (and quickly if
>possible...the train is moving very fast in this wrong direction!).
>
>First, the memory of the 400 will be used for the ODBC queries outside of
>SAP's memory management which could force swapping and invalidations of SAP
>memory objects. Second, I've heard that while long reads are occurring on
>these tables, data cannot be updated (if the read is using an index, for
>example). I'm not certain if this is true, but I think that someone on
>this
>list will definitely know. Third, I am uncertain of the overhead on
>communications, disk subsystems, memory, etc, of ODBC access. I thought
>one
>of you would know that as well. Lastly, almost all system monitoring, etc,
>currently occurs in SAP, whereas this proposal would force more monitoring
>on AS/400 server level.
>
>Any clarification on the above objections are greatly appreciated. Also,
>any other benefits/downsides that I haven't considered?
>
>Thanks in advance,
>Mike D. Martin
>SAP Basis Administrator
>AO*SOLA
>707-763-9911 x6106
>email: <mailto:mmartinZs...>
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
Durban Tours - Südafrika Safari
PERFORM Short Reference General Material Data
This documentation is copyright by SAP AG.
Length: 4722 Date: 20240419 Time: 155010 sap01-206 ( 2 ms )
