×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

OO Rexx and DB2 LUW

OO Rexx and DB2 LUW

OO Rexx and DB2 LUW

(OP)
Has anyone written a rexx that uses data stored in DB2 LUW?

RE: OO Rexx and DB2 LUW

I use REXX in two scenarios:
#1 The REXX program runs on iSeries and uses data stored on DB2 on IBM iSeries
#2 The REXX program runs on a Windows PC and uses data stored in DB2 on IBM iSeries

In scenario #2 I use the Open Object REXX interpreter. To connect from Windows PC to DB2 database which is running on the other computer, the ADODB.Connection is used.
I don't have DB2 LUW but IMO to work with it a similar method can be used as I'm using now.

What's your problem and how can we help you?

RE: OO Rexx and DB2 LUW

(OP)
mikrom, thanks for the quick reply. I am currently an app programmer on IBM Z/OS. I use rexx extensively to process data on DB2 for z/os. I want to prepare to do similar things with my own personal data. I want to leverage my current skills to do this. I am trying to determine what is the best and most economical software to obtain to do this on my small windows machine.

I am thinking of downloading OOREXX and free DB2 for LUW. Does this make sense? I kinda wanted to see an example of how this would be done. My programming experience on anything but the mainframe is minimal if at all - a little MS Access years ago.

Your thoughts/suggestions are welcome. Examples are great.

RE: OO Rexx and DB2 LUW

Hi mkmc,

Here is an example how to read data stored in DB2 on IBM iSeries from Open Object REXX program running on Windows PC.

In DB2 database running on IBM iSeries (aka AS/400) I have a Database schema in which I have a table. I want to connect from my Windows PC using userid and password to the database, selecting from the table some records and save them into a CSV file on my PC.

Here is the OO REXX script:

db_example_db2_as400.rex

CODE

/********************************************/
/* Reading records and fields from database */
/********************************************/

/*## Enter Login-Data */
csebk  = raw_input("AS/400 name : ")
schema = raw_input("DB schema   : ")
userid = raw_input("User Id     : ")
/* Using getpass for getting password */
pwd    = getpass("Password    : ")

/*## using ADO */
/* Connection String */
connection_string="PROVIDER=IBMDA400; DATA SOURCE="csebk||,
                  "; USER ID="userid"; PASSWORD="pwd||,
                  "; DEFAULT COLLECTION = "schema

/* Open ADO connection */ 
connection = .OLEObject~New("ADODB.Connection")
connection~open(connection_string)

/* Create an instance of an ADO Recordset */
recordset   = .OLEObject~New("ADODB.Recordset")
sql_stmt =  "select * "||,
            "from P001800V "||,
            "where OBEC like '%Mesto%'"
/* Open the recordset, using an SQL statement and the existing ADO connection */
recordset~open(sql_stmt, connection, 1, 3)

/* Create and populate an array of field names */
fields_count = recordset~Fields~Count
fields = ""
do j=1 to fields_count
  if j < fields_count then 
    fields = fields || recordset~Fields(j-1)~Name || " "
  else
    fields = fields || recordset~Fields(j-1)~Name
end

csv_file = "mesta.csv"
/* Open CSV file */
say "Now writing data to CSV file '" || csv_file || "'.." 

/* Header line */
line = translate(fields,";"," ")
call lineout csv_file, line, 1

/* Data lines */
do while (\ recordset~EOF)
  line = ""
  do j=1 to words(fields)
    fieldname = word(fields, j)
    /* join field values into string */
    if j < fields_count then
      line = line || recordset~Fields~Item(fieldname)~Value || ";"
    else
      line = line || recordset~Fields~Item(fieldname)~Value
  end
  call lineout csv_file, line
  recordset~MoveNext 
end
say "..done."

/* Close CSV file */
call lineout csv_file
/* Close RecordSet */
recordset~close()
/* Close Connection */
connection~close()
exit


/**************** Functions ****************/
raw_input: procedure
  /* function raw_input() */
  use arg prompt
  call charout , prompt
  parse pull inp
  return inp

getpass: procedure
  /* print the prompt */
  call charout , arg(1)
  /* define some ASCII constants */
  EnterKey     ='0D'X /* ASCII Enter i.e. Carriage Return */
  BackSpaceKey ='08'X /* ASCII BackSpace */
  BeepSound    ='07'X /* ASCII Beep sound */ 
  pwd = '' 
  do forever 
    char = SysGetKey('NOECHO')
    if char=EnterKey then leave
    if char=BackSpaceKey then
      if length(pwd)>0 then
        do
          pwd=substr(pwd,1,length(pwd)-1)
          /* Move Cursor Back */
          call charout , BackSpaceKey
          call charout ,' '
          call charout , BackSpaceKey
        end
      else
        call charout , BeepSound 
    else 
      do  
        call charout ,'*' 
        pwd = pwd''char
      end 
  end 
  say 
  return pwd 

RE: OO Rexx and DB2 LUW

Hi mkmc,

You mentioned, that you have MS Access on your PC. So you can use it easily too.
I created in MS Access on my PC a simple database with one table named same and with the same data structure, as I have on DB2 on AS/400. I saved the database in a file named Exercise.accdb, in the same directory where my REXX scripts reside.
My access database is not password protected, I do not need schema name, userid and password in my connection string. So I can simplify the REXX script given above, omitting the subroutines for reading schema name, userid and password and changing connection string for AS/400 to the appropriate connection string for MS Access. Everything else stay the same.

Here is the OO REXX script for MS Access:

db_example_ms_access.rex

CODE

/********************************************/
/* Reading records and fields from database */
/********************************************/

/*## using ADO */
/* Connection String */
connection_string="PROVIDER=Microsoft.ACE.OLEDB.12.0"||,
                  "; DATA SOURCE=Exercise.accdb"||,
                  "; Persist Security Info=False;"

/* Open ADO connection */ 
connection = .OLEObject~New("ADODB.Connection")
connection~open(connection_string)

/* Create an instance of an ADO Recordset */
recordset   = .OLEObject~New("ADODB.Recordset")
sql_stmt =  "select * "||,
            "from P001800V "||,
            "where OBEC like '%Mesto%'"
/* Open the recordset, using an SQL statement and the existing ADO connection */
recordset~open(sql_stmt, connection, 1, 3)

/* Create and populate an array of field names */
fields_count = recordset~Fields~Count
fields = ""
do j=1 to fields_count
  if j < fields_count then 
    fields = fields || recordset~Fields(j-1)~Name || " "
  else
    fields = fields || recordset~Fields(j-1)~Name
end

csv_file = "mesta.csv"
/* Open CSV file */
say "Now writing data to CSV file '" || csv_file || "'.." 

/* Header line */
line = translate(fields,";"," ")
call lineout csv_file, line, 1

/* Data lines */
do while (\ recordset~EOF)
  line = ""
  do j=1 to words(fields)
    fieldname = word(fields, j)
    /* join field values into string */
    if j < fields_count then
      line = line || recordset~Fields~Item(fieldname)~Value || ";"
    else
      line = line || recordset~Fields~Item(fieldname)~Value
  end
  call lineout csv_file, line
  recordset~MoveNext 
end
say "..done."

/* Close CSV file */
call lineout csv_file
/* Close RecordSet */
recordset~close()
/* Close Connection */
connection~close()
exit 

RE: OO Rexx and DB2 LUW

Hi mkmc,

For a simple home/hobbyist/educational usage I would prefer a simple database without need to start and stop a database server.

If you don't want to buy MS Access, you can also use SQLite. It's free and very good database. You can download an install an ODBC driver sqliteodbc_w64.exe here SQLite ODBC Driver
To connect and work with with SQLite at the same way as I posted above (ADO) you have to use Microsoft OLE DB Provider for ODBC. I could post you an example for using SQLite in OO REXX.

RE: OO Rexx and DB2 LUW

(OP)
Thanks, mikrom. The SQLite example would be great.

RE: OO Rexx and DB2 LUW

Hi mkmc,

With SQLite it's almost the same as with Access. I have an example Guinea Pigs Database. It's saved in a file PIGS_DB in my directory and contains table GUINEA_PIGS from which I want to select some records and save them into a CSV-file. So after I installed the SQLite driver mentioned above I only need to change the connection string, the select statement and the name of the CSV file, everything else stay the same.

db_example_sqlite.rex

CODE

/********************************************/
/* Reading records and fields from database */
/********************************************/

/*## using ADO */
/* Connection String */
connection_string="PROVIDER=MSDASQL; DRIVER={SQLite3 ODBC Driver}"||,
                  "; DATABASE=c:\00_mikrom\Work\PIGS_DB"
/* Open ADO connection */ 
connection = .OLEObject~New("ADODB.Connection")
connection~open(connection_string)

/* Create an instance of an ADO Recordset */
recordset   = .OLEObject~New("ADODB.Recordset")
sql_stmt =  "select * "||,
            "from GUINEA_PIGS "||,
            "where NAME like '%Sisi%'"
/* Open the recordset, using an SQL statement and the existing ADO connection */
recordset~open(sql_stmt, connection, 1, 3)

/* Create and populate an array of field names */
fields_count = recordset~Fields~Count
fields = ""
do j=1 to fields_count
  if j < fields_count then 
    fields = fields || recordset~Fields(j-1)~Name || " "
  else
    fields = fields || recordset~Fields(j-1)~Name
end

csv_file = "pigs.csv"
/* Open CSV file */
say "Now writing data to CSV file '" || csv_file || "'.." 

/* Header line */
line = translate(fields,";"," ")
call lineout csv_file, line, 1

/* Data lines */
do while (\ recordset~EOF)
  line = ""
  do j=1 to words(fields)
    fieldname = word(fields, j)
    /* join field values into string */
    if j < fields_count then
      line = line || recordset~Fields~Item(fieldname)~Value || ";"
    else
      line = line || recordset~Fields~Item(fieldname)~Value
  end
  call lineout csv_file, line
  recordset~MoveNext 
end
say "..done."

/* Close CSV file */
call lineout csv_file
/* Close RecordSet */
recordset~close()
/* Close Connection */
connection~close()
exit 

Btw for working with databases, I'm using this free database tool:
https://dbeaver.io/
It has build in SQLite driver and contains sample database, which you can experiment with.
Without need to install any other tools, you can create with it your own SQLite database, and maintain it using SQL - i.e. create and drop tables, insert data, update data, select data.

RE: OO Rexx and DB2 LUW

Hi mkmc,

Then tell, which is more attractive to you, MS Access or SQLite, or do you still strictly insist that it need to be only DB2?
smile

RE: OO Rexx and DB2 LUW

(OP)
Thanks mikrom. When I do this I will probably go with SQLite. It does appear a bit different as compared to rexx and db2 in z/os:
- The connection
- lineout?
- In zos, you use the address command to code for different command environments.
- No DSNREXX
- No cursor
- No fetching the cursor
etc.

Its all good. I can figure this stuff out.

I really appreciate it.

RE: OO Rexx and DB2 LUW

Hi mkmc,

Yes on PC it's very different. On IBM i we use ADDRESS, EXECSQL, CURSOR, FETCH too - for example you can look what I posted on 4 Nov 09 09:47 here https://www.tek-tips.com/viewthread.cfm?qid=157070...

LINEIN and LINEOUT are standard ANSI REXX functions for reading and writing text files, available in OO REXX and Regina REXX interpreters
see:
https://www.tek-tips.com/viewthread.cfm?qid=148593...
http://manmrk.net/tutorials/rexx/oorexx/rexxref42/...
https://www.rexxla.org/rexxlang/standards/j18pub.p...

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close