RE: [SPAM] SQL SERVER/ISERIES INTEGRATION

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

Charles, 

Per:

http://msdn.microsoft.com/library/en-us/tsqlref/ts_set-set_74bw.asp?frame=tr
ue

one cannot set it to NONE and I don't think NONE is SQL standard either.
However, due to legacy apps on the 400 not using commitment control or
journaling, NONE was introduced in DB2/400. 
Rosalie is creating and inserting into a table on the 400 so the command
should be perfectly valid (i.e. via ODBC connection).

You are correct on journaling just the table she created, that would
definitely work.  You're also right on no planning required for that
solution.

When I said planning I was really thinking of changing the application to do
it the "right" way (per SQL standards) -- establishing commitment control
boundaries, selecting which tables need journaling and which not etc.  All
good things but require planning and strategic placement.

Elvis


-----Original Message-----
Subject: RE: [SPAM] SQL SERVER/ISERIES INTEGRATION

Elvis,

Can you use SET TRANSACTION ISOLATION LEVEL NONE on SQL server???

I didn't see any mention of this being valid during my quick scan of the
documentation.


Also, just wanted to point out that the original poster doesn't need to
journal the entire library.  Just the files he is trying to write to from
SQL server.  No real planning is required.

Charles Wilt
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 

> -----Original Message-----
> From: Elvis Budimlic [mailto:ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx]
> Sent: Tuesday, November 09, 2004 10:43 AM
> To: 'Midrange Systems Technical Discussion'
> Subject: RE: [SPAM] SQL SERVER/ISERIES INTEGRATION
> 
> 
> Rosalie,
> 
> CREATE COLLECTION and CREATE SCHEMA are SQL standards and 
> automatically
> start journaling in that collection/schema/library, again per 
> SQL standards.
> CRTLIB is OS400 native command and does not comply with SQL 
> standards (nor
> should it), so it does not automatically start journaling.
> 
> By default, most ODBC or JDBC drivers have AutoCommit set to true and
> Transaction Level set to something other than NONE (NC).
> 
> If you want to create new table in a library created using 
> CRTLIB command
> and update its data (i.e. via INSERT), then you need to tell the SQL
> driver/server/connection not to AutoCommit and set the 
> Transaction Level to
> NONE.
> 
> SQL command is SET TRANSACTION ISOLATION LEVEL NONE.
> 
> Alternatively, you could start journaling on that library, 
> but that is more
> involved solution and requires some planning.  Not to say that there's
> anything wrong with it :)
> 
> Elvis
> 
> 
> -----Original Message-----
> Subject: [SPAM] SQL SERVER/ISERIES INTEGRATION
> 
> I am trying to export data from a table on a sql server 
> database using a
> linked server to our iSeries.  The table gets created however 
> it does not
> populate the data and I get the following message :-
> 
> (Embedded image moved to file: pic12859.pcx)
> 
> None of the files or libraries that we need to populate on 
> the iSeries are
> journaled and we cannot journal them as they are part of an 
> ERP package.
> When I create a library through a SQL statement using "create 
> collection
> testlib" and try to populate the table it then works, however 
> when trying
> to update an existing table in an existing library it does 
> not.  I notice
> that the library "testlib" is created with journals.
> Can anyone help please?
> 
> Rosalie Ducasse
> 
> 
>