Last active
September 16, 2020 09:01
-
-
Save robinsg/ffddb9626c9d3735517cb62cb5e9514c to your computer and use it in GitHub Desktop.
Ansible for i - Setup user and run SQL statements
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--- | |
# install_ptfs_via_imgclg | |
- name: Set up a user, library and then run SQL statements | |
hosts: all | |
vars: | |
usrprf: 'ROBINSG' | |
usrcls: '*USER' | |
homedir: '/home/robinsg' | |
curlib: "{{ usrprf }}" | |
text: 'Glenn Robinson' | |
public_auth: '*NONE' | |
srclib: 'QIWS' | |
srcfile: 'QCUSTCDT' | |
jrn: 'ANSIBJRN' | |
jrnrcv: 'ANSIBRCV01' | |
sshfile: 'ansible_id_rsa.pub' | |
gather_facts: no | |
collections: | |
- ibm.power_ibmi | |
tasks: | |
- block: | |
- name: Does the user profile or library already exist? | |
ibmi_object_find: | |
object_name: "{{ item.obj }}" | |
lib_name: "{{ item.lib }}" | |
object_type_list: "{{ item.type }}" | |
register: object_find | |
with_items: | |
- { obj: "{{ usrprf }}", lib: 'QSYS', type: '*USRPRF'} | |
- { obj: "{{ curlib }}", lib: 'QSYS', type: '*LIB'} | |
- name: If user profile or library already exists then fail assertion and do not continue | |
assert: | |
that: | |
- "{{ item.object_list | length }} == 0" | |
with_items: "{{ object_find.results }}" | |
tags: | |
- pre_checks | |
- block: | |
- name: Create the user profile | |
ibmi_user_and_group: | |
operation: create | |
user: "{{ usrprf }}" | |
user_class: "{{ usrcls }}" | |
status: '*ENABLED' | |
owner: '*USRPRF' | |
text: "{{ text }}" | |
parameters: "HOMEDIR('{{ homedir }}') CURLIB({{ curlib }})" # Additional aoostophes required to correct IFS format in HOMEDIR parameter | |
- name: Create the user's home directory | |
file: | |
path: "{{ homedir }}" | |
state: directory | |
owner: "{{ usrprf }}" | |
mode: '0744' | |
- name: Create a library for the user | |
ibmi_cl_command: | |
cmd: "CRTLIB LIB({{ curlib }})" | |
- name: Copy the database file in to the library created | |
ibmi_cl_command: | |
cmd: "CPYF FROMFILE({{ srclib }}/{{ srcfile }}) TOFILE({{ curlib }}/{{ srcfile }}) CRTFILE(*YES)" | |
- name: Create journal receiver | |
ibmi_cl_command: | |
cmd: CRTJRNRCV JRNRCV({{ curlib }}/{{ jrnrcv }}) TEXT('JOURNAL RECEIVER FOR ANSIBLE') | |
- name: Create journal | |
ibmi_cl_command: | |
cmd: CRTJRN JRN({{ curlib }}/{{ jrn }}) JRNRCV({{ curlib }}/{{ jrnrcv }}) TEXT('JOURNAL FOR ANSIBLE') | |
- name: Start journal for file | |
ibmi_cl_command: | |
cmd: STRJRNPF FILE({{ curlib }}/{{ srcfile }}) JRN({{ curlib }}/{{ jrn }}) | |
- name: Change the owner of the library to the user profile name we created | |
ibmi_cl_command: | |
cmd: "CHGOBJOWN OBJ({{ curlib }}) OBJTYPE(*LIB) NEWOWN({{ usrprf }}) CUROWNAUT(*REVOKE)" | |
- name: Change public authority to *EXCLUDE on the library | |
ibmi_object_authority: | |
operation: revoke | |
object_name: "{{ curlib }}" | |
object_library: "*LIBL" | |
object_type: "*LIB" | |
user: | |
- '*PUBLIC' | |
authority: | |
- '*ALL' | |
- name: Change the owner of the objects to the user profile name we created | |
ibmi_cl_command: | |
cmd: "CHGOBJOWN OBJ({{ item.lib }}/{{ item.obj }}) OBJTYPE({{ item.type }}) NEWOWN({{ usrprf }}) CUROWNAUT(*REVOKE)" | |
with_items: | |
- { obj: "{{ srcfile }}", lib: "{{ curlib }}", type: '*FILE' } | |
- { obj: "{{ jrn }}", lib: "{{ curlib }}", type: '*JRN' } | |
- { obj: "{{ jrnrcv }}", lib: "{{ curlib }}", type: '*JRNRCV' } | |
- name: Change public authority to *EXCLUDE on the object in the library | |
ibmi_object_authority: | |
operation: revoke | |
object_name: "*ALL" | |
object_library: "{{ curlib }}" | |
object_type: "*ALL" | |
user: | |
- '*PUBLIC' | |
authority: | |
- '*ALL' | |
- name: Set authorized key for the user we created taken from local rsa file | |
authorized_key: | |
user: "{{ usrprf }}" | |
state: present | |
key: "{{ lookup('file', '/home/{{ usrprf | lower }}/.ssh/{{ sshfile }}') }}" | |
tags: | |
- setup_objects | |
# Now switch to run the SQL statements as the new user created | |
- block: | |
- name: Set the user name | |
set_fact: | |
ansible_ssh_user: "{{ usrprf }}" | |
- name: Run an SQL SELECT statement | |
ibmi_sql_query: | |
sql: "SELECT * FROM {{ curlib }}.{{ srcfile }} | |
WHERE CDTLMT > 999.99" | |
register: sql_resultset | |
- name: Display the SQL query result set | |
debug: | |
msg: "{{ sql_resultset.row }}" | |
- name: Display the SQL query rows found | |
debug: | |
msg: "{{ sql_resultset.row_count }} rows found." | |
- name: Run an SQL DELETE statement | |
ibmi_sql_execute: | |
sql: "DELETE FROM {{ curlib }}.{{ srcfile }} | |
WHERE CDTLMT < 1000.00" | |
register: sql_delete | |
- block: | |
- name: Display the entire file | |
ibmi_sql_query: | |
sql: "SELECT * FROM {{ curlib }}.{{ srcfile }}" | |
register: sql_resultset | |
- name: Display results | |
debug: | |
msg: "{{ sql_resultset.row }}" | |
- name: Display rows found | |
debug: | |
msg: "{{ sql_resultset.row_count }} found." | |
when: sql_delete.rc == 0 | |
tags: | |
- sql | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment