I need to dump information to various tables in a mysql database
either from an IBM TPF mainframe database, or from text in a pdf file
that was generated from the same mainframe. I am not attempting any
sort of db conversion, I just need data from the mainframe. My 
question is what would be the best and or easiest way (given the two
options) to do this? I know 'datainline' probably would be used to
actually carry it out with mysql, but I have no idea on the above
mentioned areas.

The following is what I have found while researching the two options:

    The only way to access the TPF mainframe is through a dial up    
connection using a terminal emulation program.  Complex compiler code
could be necessary to carry out the data dump.

   Extracting text from a pdf file can sometimes be difficult and
might create inaccurate data.  There is software or other means that
can perform text extraction from pdf files, but I have yet to find one
that is suited to my needs.

I have spent quite some time trying to figure this out on my own, and
as you can probably tell my research hasn't yielded much.
    
The work would be done by myself using Cold Fusion as the middleware. 
Just to let you know this is a piece of a larger project and is the
most advanced project I have attempted (most of my experience is in an
academic setting).  I am comfortable with tasks such as this, but
working with the mainframe technology has me a bit nervous.

In your answer please include any websites, publications and other
resources that will help me understand your answer (I am always eager
to learn!).

Thanks in advance for your answer.

---

Request for Question Clarification by kyrie26-ga on 26 Sep 2004 20:07 PDT

Hi there!

Are you able to generate any other kinds of output from this database,
particularly tab-separated values (TSV) text? It sounds like you are
able to log in to the mainframe system. How much access do you have,
to run queries? If we can figure out this part, the rest will be a
piece of cake. Do let me know.

Thanks!

kyrie26-ga

---

Clarification of Question by thekid29-ga on 27 Sep 2004 01:15 PDT

kyrie,

I can't get a tsv or a csv, but part of the information that I need IS
located in an .arc or "archived" file.  I think this would take a
dearcing program.  The information in the .arc file would suffice,
however the other data I need from the mainframe is dynamic, in that
it would change daily to weekly.  Yes, I do have access to the
mainframe and my privillages allow me to run queries whenever need be,
however the output of these queries are limited to the hyperterminal. 
I am not 100% sure that the hyperterminal limits how I receive the
data.  Would it be possible that I could create a csv or tsv on my
end???

What about screen scraping? I know this practice is discouraged
because the position of things on the screen possibly could change.

My access to the mainframe is through a paid subscription with a
second party.  If I were to get the necessary information from the
mainframe in the form of a comma seperated file or tab seperated file
from the other party it would require some redtape cutting which
inherently means more time, which I dont have.

Looking forward to your ideas. 

Thanks for the help!

---

Request for Question Clarification by kyrie26-ga on 27 Sep 2004 06:45 PDT

Hi again,

What hyperterminal program are you using? 

Thanks,

kyrie26-ga

---

Request for Question Clarification by kyrie26-ga on 27 Sep 2004 09:34 PDT

Hi,

If you are using the HyperTerminal program that comes with Windows,
you might like to experiment with the Capture Text function - see if
you can accurately capture the output. If so, then you might like to
generate a query and capture the text.

I assume the database here is DB2. You might be able to generate query
results to a file, and then do a Receive File in HyperTerminal -
although I'm not too familiar with how you would do this in TPF.

Let me know how it goes.

Thanks,

kyrie26-ga

---

Clarification of Question by thekid29-ga on 27 Sep 2004 12:42 PDT

Its the Microsoft Hyperterminal.  I know that the original developors
of Hyperterminal have a more robust product out there.  I'll try the
capture text function to see what I can get.  The IBM TPF stuff is
foreign to me as well, it appears it was developed some time ago for
industries that have a high volume of transactions (hence the name
Transaction Processing Facility).

Anyway,, Ill get to it right now and see if I can come up with
something and let ya know how it goes.

Regards,

thekid29

---

Clarification of Question by thekid29-ga on 27 Sep 2004 13:22 PDT

By the way, I am able to access the mainframe with other telnet like
programs. I know I used to access this system 14 years ago using the
same program I used for acessing BBSs.  Just thought I'd let ya know
if that would help.

It is interesting that the system would dump the text/data into PDF --
PDF is a usually used due to its ability to position graphics and text
for display and print (like a digital version of something you'd
normally see on paper).  If the software is just dumping data, PDF
seems to be a poor format choice.

http://www.adobe.com/products/acrobat/adobepdf.html

All that aside, within the PDF, you might be able to select the text
and then paste it into a text file -- that would allow for easier
migration to MySQL.  An easy (and relatively unknown) option is to
interact directly with your MySQL tables from MS Access.  This can be
done using the MySQL ODBC connection:

http://www.mysql.com/products/connector/odbc/

If I were in your shoes, I would write a Perl script that collected
the info either directly from your mainframe or from the PDF and
communicated directly with the MySQL database.  The Perl modules for
PDF and MySQL:

http://search.cpan.org/~antro/PDF-111/PDF.pm
http://search.cpan.org/~rudy/DBD-mysql-2.9004/lib/DBD/mysql.pm

Further, it would seem that you could enable an Apache webserver on
the mainframe -- based on:

http://www-306.ibm.com/software/htp/tpf/download/tools.htm

-- which means you might be able to use that interface to get the PDF
or whatever out.  That would lead to an even easier path to getting
data out repeatedly.

Good luck!

KJ

Is Perl the best way to do this, or are there alternatives?  I have no
perl experience and would have to lear from the ground up.  If perl is
the only way could you reccomend a primer?

Thanks

If you can grab a PDF of the data and use Kword to format it.

http://koffice.kde.org/kword/

Most Linux distros have a copy on them.

This is how I would do it. Granted these steps would require learning
some new tools but what else is new.
1. Setup a linux box that can connect to the datasource.
2. Use "expect" http://expect.nist.gov/ to simulate your interaction
with the database program. Expect has the ability to do pattern
matches and write and read from files.

Expects name comes from the expect/send basis of its actions. It knows
to expect text X and when it see it it does Y.