view release on metacpan or  search on metacpan

lib/DBD/MVS_FTPSQL.pm  view on Meta::CPAN

  SELECT *DIST

from which you can deduct your SSID's. 

=back

Note that SDSF (System Display and Search Facility) is an IBM product which interfaces with the MVS spool that,
among other things, allows the user to list all the jobs on the spool,
not only the ones whose name starts with his user-id.


=head2 Locking and concurrency considerations

This section discusses issues related to how the intrinsic constraints of 
the "SQL through FTP" feature influence the concurrent access of data.
An explanation of the concepts behind locking, concurrency and the way DB2 implements 
it is far beyond the scope of this document and although a brief introduction of 
isolation levels is provided, that knowledge is taken for granted.
Please consult your DB2 documentation for a more thorough overview.
It is also worth checking out a couple of interesting articles of Roger E. Sanders published on Db2 
Magazine.

Ok, after having said that...       

IBM DB2 for OS/390 or later supports four levels of isolation. These, ordered from the more to the less 
restrictive, are: 

=over 4

=item Repeatable Read (RR)  

Share locks are acquired on all the rows referenced (not only
those ones that will be returned) and they are released only when the transaction is 
committed or rolled back. Other concurrent transactions
can't acquire exclusive locks on those rows (and hence will have to wait before
modify the data) until the transaction owning the locks terminates. This prevents any 
interference between transactions themselves (the same query issued multiple times within the 
same transaction will ever return the same data) but also decreases concurrency, 
causing a slow down in performance.

=item Read Stability (RS) 

Share locks are acquired only for those rows that are part of a result set.
This prevents dirty reads (the reading of uncommitted data) and nonrepeatable 
reads while phantoms phenomena (described below) can occur.
If a query is issued more than once in the same transaction, it may get additional 
(precisely phantom) rows, as another concurrent transaction can insert rows that match 
the search criteria of the query.

=item Cursor Stability (CS)  

This is the default isolation level. It locks only the row (the page) that is currently being returned. As the cursor leaves the row, the lock is released and acquired for the next one, until all the data is returned.
While this maximizes concurrency and prevents dirty reads it does not 
ensure that the data retrieved will not be changed by other transactions, so
if the transaction reads the same row of data more than once odds are it
gets different results each time (nonrepetable read phenomena).

=item Uncommitted Read (UR)

With this isolation levels the transaction (almost) doesn't acquire locks and 
doesn't check if the data that is retrieving is locked.
This, at the price of risking reading non committed data, 
leads to two main advantages:

=over 2

=item *

Better performance if compared with other isolation levels.

=item *

Ensures that a deadlock condition can not occur.

=back

=back

Notice that with this driver you can override the default isolation level 
only at query level. You can do so by ending the statement 
with a "with" clause whose syntax is:

  (fullselect)  WITH [RR|RS|CS|UR]

as illustrated by the following example:

  SELECT * FROM SYSIBM.SYSDUMMY1 WITH UR

When using IBM FTP CS as a medium to submit queries, there are two main limitations 
that affect your control over the way the data is locked and 
isolated between concurrent processes. These limitations are:

=over 2

=item *

The inability to control transaction boundaries. That is, you can only
issue select statements; every other statement - and this includes also
commit and rollback - are not permitted. To put it briefly, you can't disable 
autocommit. 


=item *

When a statement is L<execute()|DBI/execute>d, all the data requested it's transferred from 
the mainframe to the pc altogether. Subsequently, when you L<fetch()|DBI/fetchrow_arrayref> the rows,
you will interact with a local temporary copy of the data.

=back

The first condition implies it's not possible in any way to protect your application
against nonrepetable read and phantom phenomena between two different executions 
of the same query.
The second one makes de-facto the choice of RR or RS as isolation levels useless,
because while you are (locally) fetching the data, the transaction is already terminated (
the real fetching of data happens contextually to the statement execution).

Using RR as isolation level, can make a difference when your 
query (maybe with the auxilium of a subquery) accesses the same 
table more than once, like in the following example:

  select max(salary) as sal_ko from staff where 
    salary = ( select max(salary) as sal_ok from staff)

If this query it's not executed with an isolation level RR,
B<it may return a null value> instead of the maximum
salary. Let's clarify why this can take place. During execution, the table  staff is processed two 
times, first time to determine the maximum salary sal_ok and later, to check which 
salary corresponds to sal_ok.
If, between the two phases, a transaction that modifies the maximum salary is committed 
(like an update that increases the salary of that staff member) then sal_ko will not match any value.

Since RR or RS don't work as they should, this leave us with two options. Specifying CS (or omitting it as it's the default) and retrieve only 
data committed after the execution of the statement, or choosing UR and retrieve also non committed
data. Remember that, in any case, you won't see any changes, committed or not, happening while you're fetching
 data because, as stated before, you're working on a local copy of the resultset, that was internally fetched
during the execution of the query.

Although these limitations may seem harsh, it is important to realize that in the majority of the cases,
CS or also UR are the best choice, because maximize concurrency and hence performance. 
This is particularly true when retrieving data from a mainframe, because there are a lot of other processes
that accesses data, potentially more critical than your application (CICS applications for example).

=head1 EXAMPLES

=head2 Example 1: retrieving a single row of data 

  use warnings;
  use strict;
  use DBI;