During October 2018 I was attempting to modernize our data integration pipelines so we can
Given that we use SAP Data Services, the XML format file *.atl derived from the jobs happens to include each job’s detail within the <DIJob>…</DIJob> nodes. How would we extract this?
SAP-DS provides al_engine.sh which is a wrapper around the al_engine binary, the controller of the program. It is important to note that credentials provided to this script directly drive down to the RDBMS. These are not the “proxy” credentials we give developers that are hosted within the DS engine instance. Hence this requires the DS dba credentials or a custom role to interact directly with the RDBMS.
What connection framework is most suitable? isql,sqla, dsisql, dsisqlc… what do I use
Sybase worked with Microsoft from 1986 to produce SQL Server and in 1993 the Sybase and Microsoft version where identical, after 1993 the two code bases diverged. Sybase ASE and SQL Anywhere were enterprise and commodity systems respectively. This example is for SQL Anywhere…
ODBC tools can be configured to use a number of ODBC driver binaries by setting up either the /etc/odbcinit global file and the /home/$USER/.odbc.ini file local to each user.
# cat /home/$USER/.odbc.ini
[LR_REPO]
Userid=dba
DatabaseName=my_ds_repo
ServerName=my_bi_server
Host=my_hostname:2638
Password=notagoodidea
Driver=/usr/lib64/libdbodbc12.so
sqla is the query tool for this system and is an interactive ncursor style environment called by running dbisqlc .
#get your binary from here: #https://wiki.scn.sap.com/wiki/display/SQLANY/SAP+SQL+Anywhere+Database+Client+Download
wget http://d5d4ifzqzkhwt.cloudfront.net/sqla12client/sqla1201_client_linux_x86x64.tar.gz
tar xzvf sqla1201_client_linux_x86x64.tar.gz
cd client1201
sudo ./setup
sed -i '/PATH=/s/$/\:\/opt\/sqlanywhere12\/bin64/’ ~/.bash_profile
#call with: `dbisqlc -c "DSN=LR_REPO"`
The standard unix utility for the ODBC protocol is fromunixODBC and called by running isql.
wget http://www.unixodbc.org/unixODBC-2.3.7.tar.gz
tar xzvf unixODBC-2.3.7.tar.gz
cd unixODBC-2.3.7
./configure --prefix=/opt
make
sudo make install
# isql LR_REPO <<<"exec sp_tables '%';"
The DS administrator will find al_engine and al_engine.sh in the bin directory of their installation of data services. And we have a dbsrv12 process running on port 2638. The simplest export of jobs could look like this:
$ al_engine.sh -XXL@J@/tmp/jobs.xml -Uusername \
-SLR_REPO -NSQL_Anywhere -P'password' \
-passphrase'passphrase_for_exported_creds'
But I don’t want to use the DBA password so I need to create a profile that will safely read only access what I need. The jobs export requires less privilege, full export requires more, I put all the required read access into this user:
isql LR_REPO <<EOF
--my new RDBMS user.
CREATE USER my_user
IDENTIFIED BY 'temp!password'
FORCE PASSWORD CHANGE ON;--without a password the exporter role cannot login
GRANT CONNECT TO exporter;--allow multiple users to run the al_engine.sh command.
GRANT GROUP TO exporter;--al_engine.sh calls a select to generate job files
--that include these objects.
--This is the minimum privilege required.GRANT SELECT ON dba.AL_VERSION TO exporter;
GRANT SELECT ON dba.AL_LANG TO exporter;
GRANT SELECT ON dba.AL_OBJ_PERMS TO exporter;
GRANT SELECT ON dba.AL_LANGTEXT TO exporter;
GRANT SELECT ON dba.AL_SCHED_INFO TO exporter;
GRANT SELECT ON dba.AL_OBJECT_ATTR TO exporter;--al_engine.sh additionally needs these rights on
--the following objects to extract everything else.GRANT SELECT ON dba.AL_LANG TO exporter;
GRANT SELECT ON dba.AL_OBJ_PERMS TO exporter;
GRANT SELECT ON dba.AL_LANGTEXT TO exporter;
GRANT SELECT ON dba.AL_SCHED_INFO TO exporter;
GRANT SELECT ON dba.AL_OBJECT_ATTR TO exporter;
GRANT SELECT ON dba.AL_OPTIONS TO exporter;
GRANT SELECT ON dba.AL_OPTION_DESC TO exporter;
GRANT SELECT ON dba.AL_OPTION_VALUES TO exporter;
GRANT SELECT ON dba.AL_ATTR TO exporter;
GRANT SELECT ON dba.AL_OVERFLOW_ATTR TO exporter;
GRANT SELECT ON dba.AL_FUNCINFO TO exporter;
GRANT SELECT ON dba.AL_EXT_FUNCTEXT TO exporter;
GRANT SELECT ON dba.AL_SCHEMA TO exporter;
GRANT SELECT ON dba.AL_COLUMN TO exporter;
GRANT SELECT ON dba.AL_PKEY TO exporter;
GRANT SELECT ON dba.AL_INDEX TO exporter;
GRANT SELECT ON dba.AL_FKPKREL TO exporter;
GRANT SELECT ON dba.AL_PCOLUMN TO exporter;
GRANT SELECT ON dba.AL_DBNAME_MAPPING TO exporter;
GRANT SELECT ON dba.AL_DOMAIN_INFO TO exporter;
GRANT SELECT ON dba.AL_RELATION TO exporter;
GRANT SELECT ON dba.AL_FUNCPARAM TO exporter;
GRANT SELECT ON dba.AL_PROJECTS TO exporter;
GRANT SELECT ON dba.AL_PROJMEMS TO exporter;
GRANT SELECT ON dba.AL_RELATION TO exporter;--can add future users later
GRANT MEMBERSHIP IN GROUP exporter TO my_user;--now anything run with a ‘dba’ owner by this user can be assumed.
--statements owned by ‘dba’ previously cannot be
--implicitly resolved (SELECT * FROM dba.AL_LANG failed)
GRANT GROUP TO dba;
GRANT MEMBERSHIP IN GROUP dba TO exporter;
EOF
You’ll find that /tmp/jobs.xml is about 300–500 lines per job exported
This should allow you to put jobs into source control and deploy using the same al_engine.sh script