RE: Data Transfer Tool -- MIDRANGE-L

Thanks Rob,
Just to clarify: The "always running program" for data transfer runs on our
SQL server (written in VBA with an Access front end). 
When you remotely log on to the SQL server that houses the "always running
program", it becomes a mess. Some users can see it, some can't. It is set to
automatically start under the administrator account when we reboot our
server on a weekly basis, and if you happen to log on remotely as
administrator while it is transferring data, another instance of it starts
up and you have data issues while the war over which instance is going to
handle the data occurs.
It is an accident waiting to happen.
The AS400 handles all of this beautifully, but I know there has to be an
easier, less dangerous way to do this.
It is really critical to have the "real-time" transfers from the SQL
database to the AS400, as production data will be transmitted as users are
using handheld devices continuously on the shop floor.

Thank you,
Tena



-----Original Message-----
From: rob@xxxxxxxxx [mailto:rob@xxxxxxxxx] 
Sent: Tuesday, October 12, 2004 9:37 AM
To: Midrange Systems Technical Discussion
Subject: RE: Data Transfer Tool

Whoever set this up did a fine job.  I can see a good case for a setup 
like this.  If the iSeries is doing a critical backup, period end 
processing, or some such thing, then the PC continues to load the stuff 
into the data queue.  When the backup is done it fires back up the data 
queue job.  It catches up and waits on the next data queue entry.  By 
replicating the data on the PC production can continue during these times 
of data outages.  A reasonable data queue program is very close to 'real 
time'.  I've written some myself.  We used to have such a setup when we 
did wire manufacturing and had shop floor computers that fed constant 
information to the AS/400.

As far as not seeing the job associated with the user...   There will 2 or 
3 jobs.  One job will be the data queue processor.  That will run under 
one user.  And not necessarily the user associated with the PC.  Then 
there will be the job that communicates with the PC.  This may be running 
under some IBM type user.  However if you do a WRKOBJLCK OBJ(ROB) 
OBJTYPE(*USRPRF) you will see some jobs that do not have that users name. 
Like the following
Job          User 
QZLSFILE     QUSER
ROBS1        ROB 
Looking further at the first job you will see something in it's joblog 
like
Servicing user profile ROB from client xxx.xxx.xxx.xxx.
This is how you find the job associated with the PC user.
Finding it, and getting any useful information out of it, are two 
different things.
Now, the third job is an optional job that may happen if the data queue 
processor submits another job to actually post the data queue entry versus 
doing the posting within the data queue processor itself.  You could go 
either way depending on your unique business needs.

Now, back to jobs that run automatically, do you mean that when data is 
updated on your 400 you want it to automatically feed down to your PC or 
do you want to automatically submit the data queue processor that posts 
the data from the pc to the 400?

If you are thinking the former, you could be a trigger on the data bases 
on the 400.  When a record is updated it could automatically post the 
change to the data queue.  And when your PC checks the data queue it's 
ready to post.  As far as the latter, I don't think that's a good idea. 
The data queue processor on the 400 might be down because of a critical 
backup, etc.  Instead of looking for it by user id, try looking for it by 
job.  Like WRKJOB DATAQP or whatever the job name is.  On certain critical 
never ending jobs we've been known to put out other job monitors that will 
alert people when they are not running.  We 'rolled our own'.  But if you 
have a tool like ROBOT they might have something like this.

Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





Tena Hewes <THewes@xxxxxxxxxxx> 
Sent by: midrange-l-bounces@xxxxxxxxxxxx
10/12/2004 06:43 AM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
"'Midrange Systems Technical Discussion'" <midrange-l@xxxxxxxxxxxx>
cc

Fax to

Subject
RE: Data Transfer Tool






Phew! Ok, let me see if I can clarify...
We have an application that runs on SQL server (An SQL database w/ an 
Access
user interface). 
Currently, we have a program running in Access (VBA) on our SQL server 
that
"looks" at the AS400 DQ (out) every minute to determine if there is any 
data
needing to be transferred. When there is, it is imported in to a "holding"
table, where a stored procedure processes it. We also need to do the same
thing backwards. There is another holding table in our SQL application 
that
stores the data waiting to be transmitted back to the AS400 and sends it 
to
another AS400 DQ (in) where the AS400 takes over. 
This whole process is in place to make sure data (Inventory, PO's, Orders,
etc.) is synced up on an ongoing basis. Timed data feeds going from the
AS400 to our SQL app. are reasonable, but coming from the app. to the 
AS400
need to be "real-time".
Ok, the problem is this interface program between the AS400 and the SQL
server does not make me feel "warm and fuzzy". It has to always be 
running,
and if you happen to log on to the server as another user, you may not see
it running on your session. I really want something "hands-off". I want to
be able to set up 'jobs' on my SQL server (or AS400, if I know what I am
doing) that run automatically, so these data queues are populated behind 
the
scenes.

In answer to the following:
"Do you have iSeries Access?"
I am assuming you mean can I get my hands on it? Yes.

"One possibility is to export the data from Access into a different file
format."
Like Excel?

"Judging by what I perceive your background to be, you might find it 
easier
to create this intermediate file in DB2 using iSeries Access Navigator
Database tool."
Where would I find this? My background (other than an AS400 user) is
completely PC based. I like pictures!!! I try to navigate in the AS400 by
clicking...:( )

"There's other little tricks, like CCSID but that's one general path to
walk."



I hope this explains some things?
Thanks so much for the responses!
Tena

"-----Original Message-----
From: michael@xxxxxxxxxxxxxxxxxx [mailto:michael@xxxxxxxxxxxxxxxxxx] 
Sent: Monday, October 11, 2004 3:49 PM
To: Midrange Systems Technical Discussion
Subject: RE: Data Transfer Tool

Hi Tena...we were all newbies once...welcome! Make sure you drop by the
Midrange-L table at CUDS Sunday night at COMMON and you'll be able to
meet some of the folks that frequent this place."


--
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.