Update DB2 table With a Join
Update DB2 table With a Join Acknowledging that DB2 doesn’t allow JOINS in the update statement, utilizing MERGE INTO is the key to achieve what we want. Let’s say we …
Read MoreDb2 Database
Convert a vertical query result to horizontal
Convert a vertical query result to horizontal SID STUDENT VALUE 5 FNAME Bruce 5 LNAME Wayne 5 AGE 31 5 EMAIL bwayne@brunceneterprise.com
|
1 2 3 4 5 6 |
select max(case when STUDENT = 'FNAME' then VALUE end) as FIRST_NAME, max(case when STUDENT = 'LNAME' then VALUE end) as LAST_NAME, max(case when STUDENT = 'AGE' then VALUE end) as AGE, max(case when STUDENT = 'EMAIL' then VALUE end) as EMAIL from STUDENT_INFO WHERE SID = '5' |
Result: FIRST_NAME LAST_NAME AGE EMAIL Brunce …
Read More How to add a new Linux user with db2 access
How to add a new Linux user with db2 access
|
1 2 |
--Create a new user mkuser id=5000 pgrp=db2iadm2 groups=staff home=/home/jbond |
|
1 2 |
--set the password passwd jbond |
|
1 2 |
--Login into the user account su - jbond |
|
1 2 |
-- open the profile file to associate the db2 account to the new user vi .profile |
|
1 2 3 4 |
-- paste this in there and save if [ -f /home/db2inst2/sqllib/db2profile ]; then . /home/db2inst2/sqllib/db2profile fi |
|
1 2 3 |
-- exit the account and login again so the changes could take affect exit su - jbond |
If you need to grant the JBOND user certain privileges, the privilege assignment can …
Read More Add privileges on every db2 table at once
Add privileges on every db2 table at once Recently I ran into a problem where I needed to create a user in db2 and grant him only the SELECT privilege. …
Read More Create a variable in DB2
There’s a ton of stuff on the internet on how to create a variable. Mostly off them are suggesting to use the declare command. Sadly, that didn’t do anything for …
Read More DB2 Export and Import Data
Here are the steps on how to export and import data from and to database in the delimited (.del) format. 1. Export:
|
1 |
db2 export to EMPLOYINFO.del of del "SELECT * FROM CLOUDEX.EMPLOYINFO" |
2. Import
|
1 |
db2 import FROM EMPLOYINFO.del of del "INSERT INTO EMPLOYINFO" |
Note: if a table …
Read More DB2 Export data into INSERT Statements
Let’s say we have a table called EMPLOYINFO, and you’re asked to export its content into INSERT statements, how would you go about it? Task: Copy the contents of EMPLOYINFO …
Read More db2 – Increment values in a field sequentially
My task consisted of the following: Three fields in a table of a thousand records were blank. Some tuples/rows were filled, but some weren’t. To avoid updating these records manually …
Read More