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' | |
| #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' |
| # .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 ; |
| # .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 "[1m[1${ERROR_COLOR}m$*[0m" ; } | |
| warning() { local opts ; [ $1 = "-n" ] && { opts=$1 ; shift ; } ; echo $opts "[1m[1${WARNING_COLOR}m$*[0m" ; } | |
| info() { local opts ; [ $1 = "-n" ] && { opts=$1 ; shift ; } ; echo $opts "[1m[1${INFO_COLOR}m$*[0m" ; } | |
| verbose() { local opts ; [ $1 = "-n" ] && { opts=$1 ; shift ; } ; echo $opts "[1m[1${VERBOSE_COLOR}m$*[0m" ; } | |
| debug() { local opts ; [ $1 = "-n" ] && { opts=$1 ; shift ; } ; echo $opts "[1m[1${DEBUG_COLOR}m$*[0m" ; } | |
| highlight() { local opts ; [ $1 = "-n" ] && { opts=$1 ; shift ; } ; echo $opts "[1m[1;37m$*[0m" ; } | |
| danger() { local opts ; [ $1 = "-n" ] && { opts=$1 ; shift ; } ; echo $opts "[47m[1;5;31m$*[0m" ; } | |
| # | |
| # 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)" | |
| } |
| # .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 |
| #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 |
| #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 |
Got any other ideas? Share in the comments below
References
Tony C. CommonUG April 2015 – http://www.gateway400.org/documents/Gateway400/Handouts/IBMiFunWithShells.pdf