Call SQL on DB2

system -i “call QSYS/QZDFMDB2 parm(‘select * from lib.table’)”

Create an alias in your .bash_profile file so its easier to run the command above

alias db2=’function _db2(){ echo “Running $1”; system -i “call QSYS/QZDFMDB2 parm(‘\”$1’\”)”; };_db2’

the sql command above becomes

db2 ‘select * from lib.table’

you can even send a sql file to this

db2 < cat ‘MyFile.sql’

Search the output of the Select statement via grep

system -i “call QSYS/QZDFMDB2 parm(‘select * from lib.table’)” | grep replace_w/_search_term

View all active jobs

system WRKACTJOB

Search all active jobs by user (case insensitive)

system WRKACTJOB | grep -i username

View all command line utilities that are available to use

ls -l /QOpenSys/usr/bin/  

Create a shell script MyShellScript.sh that executes multiple commands.  Then run it by calling MyShellScript on the ssh commandline.  And theres no need to COMPILE!  like you would need to if this was a CL program.

MyShellScript.sh

#!/QOpenSys/usr/bin/bash
echo “Your running MyShellScript”
system WRKACTJOB | grep -i bob
system WRKACTJOB | grep -i sally

Setting the Path so you don’t have to type the path to your executable.  There’s the 5250 way and the SSH way.  You want to use $PATH to include the current path and then append to it by using a : (colon) as a deliminator.

5250: WRKENVVAR to setup PATH
SSH: Create .profile under /home/USERNAME , or /etc/profile, or environment file
PATH=$PATH:/QOpenSys/opt/freeware/bin/
Or run on the ssl command line: export PATH=$PATH:/QOpenSys/opt/freeware/bin/

View what your PATH and other environment variables are set to on the SSH shell

env

View process statuses (WRKACTJOB)
ps
ps -ef

View Zend Processes

ps -ef | grep -i zend

Find the JobId,User, and program of the process ids

ps -ef | grep -i zend | awk '{print "qsh_out -c '\''/usr/bin/getjobid "$2"'\''"}'

Run git after you install it from http://perzl.org/

Find your CCSID system value and if its on 65535 change your profile to use ccsid 37

system "DSPSYSVAL SYSVAL(QCCSID)"
system "CHGUSRPRF USRPRF(MYPROFILE) CCSID(37)"

Display your library list from QSH or the current shell

qsh -c '/QSYS.LIB/QSHELL.LIB/SYSTEM.PGM dsplibl'
system -i "DSPLIBL"

view a joblog

qsh_out -c "system -i 'dspjoblog job(123456/LIB/PGM)'"

Edit files with various editors (vim, nedit, vi, joe) or setup auto SFTP on a local IDE and upload your files on save to the IBMi

Restart Apache

/usr/local/zendsvr6/bin/i5_apache.sh restart

You may have to run this on the shell if you run into issues with the editor such as

xterm-256color: Unknown terminal type 
[hit return to continue] 
Visual needs addressable cursor or upline capability

or

xterm^M: Unknown terminal type
[Hit return to continue] 
[Using open mode]

You can either run this on the command line or setup your .bash_profile to setup this variable every time you log in

TERM=xterm

You’ll also want to look into setting up your .profile and .bash_profile files which are loaded when you ssh in, and run bash respectively.  These files should be created in the directory /home/MYUSER/

.bash_profile example:

# .aliases
# vim:syntax=shexit
# Reload bash aliases
alias reload="source ~/.bash_profile"
# enable color support of ls and also add handy aliases
if [ -x /usr/bin/dircolors ]; then
test -r ~/.dircolors && eval "$(dircolors -b ~/.dircolors)" || eval "$(dircolors -b)"
alias ls='ls –color=auto'
#alias dir='dir –color=auto'
#alias vdir='vdir –color=auto'
alias grep='grep –color=auto'
alias fgrep='fgrep –color=auto'
alias egrep='egrep –color=auto'
fi
# some more ls aliases
alias ll='ls -alF'
alias la='ls -A'
alias l='ls -CF'
# Directory Navigation
alias ..='cd ..'
alias …='cd ../..'
alias ….='cd ../../..'
alias …..='cd ../../../..'
alias ……='cd ../../../../..'
# exit aliases
alias bye="exit"
alias quit="exit"
# Add an "alert" alias for long running commands. Use like so:
# sleep 10; alert
alias alert='notify-send –urgency=low -i "$([ $? = 0 ] && echo terminal || echo error)" "$(history|tail -n1|sed -e '\''s/^\s*[0-9]\+\s*//;s/[;&|]\s*alert$//'\'')"'
# IBMi aliases
alias db2='function _db2(){ echo "Running $1"; system -i "call QSYS/QZDFMDB2 parm('\''$1'\'')"; };_db2'
alias wrkactjob='function _wrkactjob(){ system WRKACTJOB; };_wrkactjob'
alias zendjobs='function _zendjobs(){ ps -ef | grep -i zend; };_zendjobs'
alias qccsid='function _qccsid(){ system "DSPSYSVAL SYSVAL(QCCSID)"; };_qccsid'
alias dsplibl='function _dsplibl(){ qsh -c '\''/QSYS.LIB/QSHELL.LIB/SYSTEM.PGM dsplibl'\''; system -i "DSPLIBL"; };_dsplibl'

view raw
.aliases
hosted with ❤ by GitHub

#MAKE SURE your file is unix based and ascii encoded. CR -> ^M in unix
#Setup PATH for executing binary utilities (i.e. vi,php)
export PATH=$PATH:/QOpenSys/usr/bin:/QOpenSys/bin:/usr/local/zendsvr/bin
#Setup TERM (terminal) for vi
export TERM=xterm
#Customize the PS1 (command line Prompt) to a green color (because we're on an IBM i :D),
#with the display of
#[username@host CurrentDirectory CurrentTime]
#export PS1="\e[0;32m[\\u@\\h \\W \\@]\\$ \e[m"
#Or use this prompt style: user@host – directory – #
export PS1="\n\[\e[0;32m\]┌─[\[\e[0m\]\[\e[1;33m\]\u\[\e[0m\]\[\e[1;32m\] @ \[\e[0m\]\[\e[1;33m\]\h\[\e[0m\]\[\e[0;32m\]]─[\[\e[0m\]\[\e[1;34m\]\w\[\e[0m\]\[\e[0;32m\]]\[\e[0;32m\]─[\[\e[0m\]\[\e[0;31m\]\!\[\e[0m\]\[\e[0;32m\]]\[\e[0m\]\n\[\e[0;32m\]└─[\[\e[0m\]\[\e[1;37m\]\$\[\e[0m\]\[\e[0;32m\]]› \[\e[0m\]"
export PS1="[\u@\h \W]\$"
export PS1='\e[0;31m $PS1 \e[m'

view raw
.bash_profile
hosted with ❤ by GitHub

# .bashrc
# vim:syntax=sh
# Make vim the default editor
export EDITOR="vim"
# Temporary utility function
function _source_if_exists {
local file ;
for file ; do
[ -f "${file}" ] && source "${file}" ;
done
}
#
# Common bash invocations…
#
# (1) interactive login shells
# $- = himBH
#
# (2) non-interactive login shells
# $- = hBc
#
case $- in *i* )
# Source global definitions
_source_if_exists "/etc/bashrc" ;
# Before anything else
_source_if_exists "${HOME}/.before_dotfiles" ;
# This loads RVM into a shell session
_source_if_exists "${HOME}/.rvm/scripts/rvm" ;
# Settings for bash history
_source_if_exists "${HOME}/.history" ;
# Set the PS1 prompt for interactive shells
_source_if_exists "${HOME}/.prompting" ;
# User-specific aliases, functions and paths
_source_if_exists "${HOME}/.aliases" "${HOME}/.functions" "${HOME}/.path" "${HOME}/.profile" ;
# NVM
_source_if_exists "${HOME}/.nvm/nvm.sh" ;
# After everything else
_source_if_exists "${HOME}/.after_dotfiles" ;
esac
# Temporary utility function
unset _source_if_exists ;

view raw
.bashrc
hosted with ❤ by GitHub

# .functions
# vim:syntax=sh
#
# Functions on home path
#
function h { cd ~/$1; }
function d { cd ~/Development/$1; }
#
# ANSI colouring functions
#
ERROR_COLOR=";31"
WARNING_COLOR=";35"
INFO_COLOR=";36"
VERBOSE_COLOR=";32"
DEBUG_COLOR=";34"
error() { local opts ; [ $1 = "-n" ] && { opts=$1 ; shift ; } ; echo $opts "[1${ERROR_COLOR}m$*" ; }
warning() { local opts ; [ $1 = "-n" ] && { opts=$1 ; shift ; } ; echo $opts "[1${WARNING_COLOR}m$*" ; }
info() { local opts ; [ $1 = "-n" ] && { opts=$1 ; shift ; } ; echo $opts "[1${INFO_COLOR}m$*" ; }
verbose() { local opts ; [ $1 = "-n" ] && { opts=$1 ; shift ; } ; echo $opts "[1${VERBOSE_COLOR}m$*" ; }
debug() { local opts ; [ $1 = "-n" ] && { opts=$1 ; shift ; } ; echo $opts "[1${DEBUG_COLOR}m$*" ; }
highlight() { local opts ; [ $1 = "-n" ] && { opts=$1 ; shift ; } ; echo $opts "$*" ; }
danger() { local opts ; [ $1 = "-n" ] && { opts=$1 ; shift ; } ; echo $opts "$*" ; }
#
# cross-platform functions
#
ttitle() { echo -n "]0;$@"; }
pecho() {
for arg ; do
echo "$arg" | tr ':;' '\n\n' ;
done
}
# Make directory and move to it
mkcdr() { mkdir -p $1 && cd $1; }
# Extract
extract() {
if [ -f $1 ] ; then
case $1 in
*.tar.bz2) tar xjf $1 ;;
*.tar.gz) tar xzf $1 ;;
*.bz2) bunzip2 $1 ;;
*.rar) rar x $1 ;;
*.gz) gunzip $1 ;;
*.tar) tar xf $1 ;;
*.tbz2) tar xjf $1 ;;
*.tgz) tar xzf $1 ;;
*.zip) unzip $1 ;;
*.Z) uncompress $1 ;;
*.7z) 7z x $1 ;;
*) echo "'$1' cannot be extracted via extract()" ;;
esac
else
echo "'$1' is not a valid file"
fi
}
startzs(){
system -i "STRSBS SBSD(ZENDSVR/ZENDSVR)"
system -i "STRTCPSVR SERVER(*HTTP) HTTPSVR(ZENDSVR)"
system -i "SBMJOB CMD(CALL PGM(ZENDSVR6/ZSTRSBS)) JOB(START_ZSV) JOBD(ZENDSVR6/ZSVR_JOBD) JOBQ(QGPL/QBATCH)"
}
endzs(){
system -i "SBMJOB CMD(CALL PGM(ZENDSVR6/ZENDSBS)) JOB(STOP_ZSV) JOBD(ZENDSVR6/ZSVR_JOBD) JOBQ(QGPL/QBATCH)"
}
#Parameter ${1} is the directory you want to effect like /www/zendsvr/htdocs/ or /www/zendsvr/writeable/uploads
http-permissions() {
system -i "CHGAUT OBJ('${1}') USER(NOGROUP) DTAAUT(*RX) OBJAUT(*NONE) SUBTREE(*ALL)"
system -i "CHGPGP OBJ('${1}') NEWPGP(NOGROUP) RVKOLDAUT(*NO) SUBTREE(*ALL)"
#Usage: on the command line
#http-permissions /www/zendsvr/htdocs/
#/www/zendsvr/htdocs/ will be given permissions recursively to the apache group
}
http-write-permissions() {
system -i "CHGAUT OBJ('${1}') USER(QTMHHTTP) DTAAUT(*RWX) OBJAUT(*NONE) SUBTREE(*NO)"
}
remove-public-permissions() {
system -i "CHGAUT OBJ('${1}') USER(*PUBLIC) DTAAUT(*NONE) OBJAUT(*NONE) SUBTREE(*ALL)"
#in case someone incorrectly gave public permissions
}
developer-permissions() {
system -i "CHGOWN OBJ('${1}') NEWOWN(WEBCODERS) RVKOLDAUT(*NO) SUBTREE(*ALL)"
system -i "CHGAUT OBJ('${1}') USER(WEBCODERS) DTAAUT(*RWX) OBJAUT(*ALL) SUBTREE(*ALL)"
}

view raw
.functions
hosted with ❤ by GitHub

# .history
# vim:syntax=sh
# Larger bash history (allow 32³ entries; default is 500)
export HISTSIZE=32768
export HISTFILESIZE=$HISTSIZE
export HISTCONTROL=ignoredups:ignorespace
# Ignore some commands in history
export HISTIGNORE="ls:ls *:llcd:cd -:pwd;exit:date:* –help"
# append to the history file, don't overwrite it
shopt -s histappend

view raw
.history
hosted with ❤ by GitHub

#default profile stored in user's home directory
#setup environment variables
export PATH=$PATH:/QOpenSys/usr/bin:/QOpenSys/bin:/usr/local/zendsvr/bin
#export LIBPATH=$LIBPATH:/QOpenSys/opt/freeware/lib
export TERM=xterm
## detect if we're in a PASE shell
#/QSYS.LIB/QSHELL.LIB/UNAME.PGM > /dev/null 2>&1
#if [ $? != 0 -a "$SHELL" != "/QOpenSys/usr/bin/bash" ]
#then
# exec /QOpenSys/usr/bin/bash
#fi
umask 022

view raw
.profile
hosted with ❤ by GitHub

#profile if your using zsh (Z Shell)
export TERM=xterm
# Files created using this user profile in the shell session will not give group and other permissions (https://en.wikipedia.org/wiki/Umask)
umask go=
# or set this in sshd_config so it applies globally using
#Subsystem sftp /usr/lib/openssh/sftp-server -u 0002
#or from command line
#echo "export TERM=xterm > ~/.zprofile"
#echo "umask go=" >> ~/.zprofile

view raw
.zprofile
hosted with ❤ by GitHub

Got any other ideas?  Share in the comments below

References

Tony C. CommonUG April 2015 – http://www.gateway400.org/documents/Gateway400/Handouts/IBMiFunWithShells.pdf

Setting up SSH on IBMi

Installing Bash on IBMi

Other cool things you can do w/ Bash