Setting up sftp between two UNIX hosts

The "ssh-keygen" command on the source host has to be run in order to create a public-private key "id_dsa" and then the public key "id_dsa.pub" needs to be shipped to the desired destination host.

You only need to set this up the first time and after that key exchange will be handled automatically. No more password imbedded inside the script.

How to setup sftp without password prompt.

1. Create the keys for the origin account, i.e. the account that performs the copy:

$ cd ~/.ssh
$ ssh-keygen -t dsa
You are asked for a passphrase, do not enter a passphrase, type for empty passphrase.

2. Verify the creation of the 2 files:

~/.ssh/id_dsa
~/.ssh/id_dsa.pub

3. Copy ~/.ssh/id_dsa.pub to the destination node.

4. Login into the destination node and verify if file ~/.ssh/authorized_keys is already present, if not do:

$ cd ~/.ssh
$ mv id_dsa.pub authorized_keys

If ~/.ssh/authorized_keys is already present, it means that other nodes are authorized to access the account.

In this case, use an editor to verify ~/.ssh/authorized_keys and add/replace id_dsa.pub as needed.

Note that every entry is one single very long line.

Additional info:
The destination directory must have permission set to 750.

Overview of FTPS set-up on z/OS

A file with your high level qualifier has to be defined by the z/OS systems programmers, this file is required for secure FTP. The RACF admin has to modify your id to allow for secure FTP. (digicert).

Normally a FTP initiated on MVS uses SYS1.FTP.DATA to obtain information and instructions about the FTP controls to be used.
This "default" data set does not provide for Secure (SSL and/or TLS) FTP.
Three additional pieces of information/control are required. FTP will search automatically for that dataset (assuming you run the FTP with the RACF id of Userid).

While the three additional pieces of information/control are important, they are not enough for FTP. Consequently, it will find and use SYS1.FTP.DATA (in addition to your file) to get a complete set on information/controls.

Data contained in userid.ftp.data

FWF FALSE ; True = Fire Wall Friendly

; means data conncections will be

; setup from the FTP client to the

; FTP server

SECURE_MECHANISM TLS ; Client parm - we want SSL connec

SECURE_CTRLCONN PRIVATE ; in case the server doesn't require?

SECURE_DATACONN PRIVATE ; in case the server doesn't require?

; The Secure Server doesn't appear to

; use a secure connection to unless

; our client asks for it?



CIPHERSUITE SSL_NULL_MD5 ; 01

CIPHERSUITE SSL_NULL_SHA ; 02

CIPHERSUITE SSL_RC4_MD5_EX ; 03

CIPHERSUITE SSL_RC4_MD5 ; 04

CIPHERSUITE SSL_RC4_SHA ; 05

CIPHERSUITE SSL_RC2_MD5_EX ; 06

CIPHERSUITE SSL_DES_SHA ; 09

CIPHERSUITE SSL_3DES_SHA ; 0A



KEYRING TCPRING ; Name of the keyring for TLS

; It can be the name of an hfs

; file (name starts with /) or

; a resource name in the security

; product (e.g., RACF)



A certificate in the DER format is required from the target server. IBM supplies these certificates, find the one that is appropriate and change it to 'TRUST', default is 'NOTRUST'.

Inorder to use secure FTP to transmit data to an external entity, data channel ports will have to be opened from the server's side firewall standpoint. Ensure that your JCL points to the correct LPAR (e.g. LPAR1 or LPAR2), use WHILE LPAR07 etc.

Also, change your USERID.FTP.DATA file to include FWF FALSE (Fire-wall friendly)

To invoke a FTP from JCL, use the following:

//STEP01 EXEC PGM=FTP,PARM='-r tls ipaddress port# (EXIT',
// REGION=4096K
//INPUT DD *
userid
psw
ascii
cd /desired directory

Setting up for an unqualified call of a DB2 stored procedure

Set up a Data source via the DB2 Configuration Assistant depending on the region
such as Development, Systems, Acceptance or Production etc.
After the Data source is created, right click on it and select 'CLI Settings'.
Click on the 'Settings' tab and add a keyword called 'CurrentFunctionPath'.
In the value tab add the following:

mySCHEMA, SYSIBM, SYSFUN, SYSPROC

where "mySCHEMA" = the schema used to resolve function references and data type references in dynamic SQL statements. This option is used as part of the process for resolving unqualified function references that may have been defined in a schema name other than the current user's schema. Note that if SYSIBM is not specified then it will be added as the first element.

Test you stored proc by calling it using "PC SAS" or "QMF for Windows" or
"Business Objects" by pointing to the newly created data source and without
the schema name.

Get Data Studio generated package name - SQL

SELECT B.NAME, B.EXTERNAL_NAME AS Package_Name
FROM SYSIBM.SYSPACKAGE A, SYSIBM.SYSROUTINES B
WHERE A.NAME like 'SQL%'
AND B.EXTERNAL_NAME = A.NAME
AND B.NAME = &Stored_proc_name_within_quotes
AND A.PDSNAME = 'Your DBRM library'

IBM Data Studio - Creating/Deploying stored procedures through the different application regions:

Create 4 different projects:- “Development”, “Systems”, “Acceptance” & and “Production” on your desktop or any number depending on your installation standards. This needs to be done only once on your desktop.

Create a new stored procedure in “Development” by clicking on the stored procedures folder and then right click and select “New”, “Stored Procedure”.
Enter the name of the procedure, e.g.TESTSP and click Next…
Now add the appropriate qualifier to the procedure name
COLLID needs to be modified to reflect the region.
WLM environment also needs to be modified to reflect the region.
Modify the code appropriately, by adding in your SQL, during your edit session if there are any apparent syntax errors the editor will highlight them to bring them to your attention.
When you’ve completed the editing process press the ‘save’ button.
After the changes are made, right click on the stored procedure and click on ‘Deploy’.
Make sure that Use Current Database is selected and click Next to continue…
Under Routine options change the following:
Collection ID: to your installation standards
WLM environment: to your installation standards

Click the ‘Deploy Options’ tab and….
Modify the Build utility to your installation standard.
Click on the pull down on the right hand side of the field to display a list.
Change build utility to your installation standard.

Modifying the Bind options

Click on the … on the right hand side of the field to open the bind options dialog box

ACTION (REPLACE) ISOLATION(CS) QUALIFIER(XYZ) OWNER(XYZ) VALIDATE(BIND) CURRENTDATA(NO)

Click Finish…

Make sure there are no errors by expanding the Data Output window to full screen mode.
If there were changes to the stored procedure, make sure you see the message that the stored procedure was re-built or re-constructed.
If it is a new stored procedure, then you will see the message saying that the stored procedure was built.

Testing your stored procedure
When you have deployed your procedure with no errors, test your stored procedure by right clicking it in the Data Project Explorer window

DPROPR - change qualifier to run subscription in a higher address space

If you wish to run an important subscription in a high priority address space, and assuming that you have the subscription running in a lower priority address space, then create a new apply qualifier that is associated with this higher priority address space and run the following SQL...(make sure you deactivate the subscription before doing this)

UPDATE ASN.IBMSNAP_SUBS_SET
SET APPLY_QUAL = 'NEW_QUALIFIER'
WHERE
APPLY_QUAL = 'OLD_QUALIFIER' AND
SET_NAME = 'SET_NAME' AND
WHOS_ON_FIRST = 'S';

UPDATE ASN.IBMSNAP_SUBS_MEMBR
SET APPLY_QUAL = 'NEW_QUALIFIER'
WHERE
APPLY_QUAL = 'OLD_QUALIFIER' AND
SET_NAME = 'SET_NAME' AND
WHOS_ON_FIRST = 'S';

UPDATE ASN.IBMSNAP_SUBS_COLS
SET APPLY_QUAL = 'NEW_QUALIFIER'
WHERE
APPLY_QUAL = 'OLD_QUALIFIER' AND
SET_NAME = 'SET_NAME' AND
WHOS_ON_FIRST = 'S';

UPDATE ASN.IBMSNAP_SUBS_STMTS
SET APPLY_QUAL = 'NEW_QUALIFIER'
WHERE
APPLY_QUAL = 'OLD_QUALIFIER' AND
SET_NAME = 'SET_NAME' AND
WHOS_ON_FIRST = 'S';

UPDATE ASN.IBMSNAP_PRUNE_SET
SET APPLY_QUAL = 'NEW_QUALIFIER'
WHERE
APPLY_QUAL = 'OLD_QUALIFIER' AND
SET_NAME = 'SET_NAME' AND
TARGET_SERVER = 'tgtserver';

UPDATE ASN.IBMSNAP_PRUNCNTL
SET APPLY_QUAL = 'NEW_QUALIFIER'
WHERE
APPLY_QUAL = 'OLD_QUALIFIER' AND
SET_NAME = 'SET_NAME' AND
TARGET_SERVER = 'tgtserver';