According to db2level, we're currently running DB2 v11.5.7.0 (on RHEL 7.9). It was upgraded from 11.5 before I arrived. This might sound like a stupid question, but are the V11.5 files required for V11.5.7.0, or is V11.5.7.0 fully stand-alone so that I can remove /opt/ibm/db2/V11.5? (I think the answer is "yes", but there's no test system for me to experiment on, and I don't want to break production.) Both V11.5/ and V11.5.7.0 see to be full installations: $ du -cm -d1 /opt/ibm/db2 du: cannot read directory ‘/opt/ibm/db2/V11.5/.licbkup’: Permission denied 2270 /opt/ibm/db2/V11.5 du: cannot read directory ‘/opt/ibm/db2/V11.5.7.0/.licbkup’: Permission denied 2150 /opt/ibm/db2/V11.5.7.0 4420 /opt/ibm/db2 4420 total More details: $ /sbin/lsof -u db2dlp01 | grep V11.5/ $ echo $? 1 $ /sbin/lsof -u db2dlp01 | grep -q V11.5.7.0 $ echo $? 0 $ /opt/ibm/db2/V11.5/bin/db2greg -dump S,DB2,11.5.0.0,/opt/ibm/db2/V11.5,,,0,0,,1610467335,0 V,DB2GPRF,DB2SYSTEM,ewr0vlpudb01dol,/opt/ibm/db2/V11.5, S,TSA,4.1.0.7,/opt/IBM/tsamp,DG_NOT_ALLOWED,DB2_INSTALLED,0,0,-,1651371491,0 S,RSCT,3.2.6.2,/usr/sbin/rsct,DG_NOT_ALLOWED,DB2_INSTALLED,0,0,-,1651371491,0 S,DB2,11.5.7.0,/opt/ibm/db2/V11.5.7.0,,,0,0,,1651371660,0 V,DB2GPRF,DB2SYSTEM,ewr0vlpudb01dol,/opt/ibm/db2/V11.5.7.0, I,DB2,11.5.7.0,db2dlp01,/home/db2dlp01/sqllib,,1,0,/opt/ibm/db2/V11.5.7.0,, $ /opt/ibm/db2/V11.5.7.0/bin/db2greg -dump S,DB2,11.5.0.0,/opt/ibm/db2/V11.5,,,0,0,,1610467335,0 V,DB2GPRF,DB2SYSTEM,ewr0vlpudb01dol,/opt/ibm/db2/V11.5, S,TSA,4.1.0.7,/opt/IBM/tsamp,DG_NOT_ALLOWED,DB2_INSTALLED,0,0,-,1651371491,0 S,RSCT,3.2.6.2,/usr/sbin/rsct,DG_NOT_ALLOWED,DB2_INSTALLED,0,0,-,1651371491,0 S,DB2,11.5.7.0,/opt/ibm/db2/V11.5.7.0,,,0,0,,1651371660,0 V,DB2GPRF,DB2SYSTEM,ewr0vlpudb01dol,/opt/ibm/db2/V11.5.7.0, I,DB2,11.5.7.0,db2dlp01,/home/db2dlp01/sqllib,,1,0,/opt/ibm/db2/V11.5.7.0,, More info: $ /usr/local/bin/db2ls Install Path Level Fix Pack Special Install Number Install Date Installer UID --------------------------------------------------------------------------------------------------------------------- /opt/ibm/db2/V11.5 11.5.0.0 0 Tue Jan 12 11:02:15 2021 EST 0 /opt/ibm/db2/V11.5.7.0 11.5.7.0 0 Sat Apr 30 22:21:00 2022 EDT 0

For a quick test, I need a DB2 instance on an older server that runs WS2016. Previously, I only ever installed DB2 express up to v. 10, on desktop windows (7, 8, or 10), and they never gave me any attitude: installed, connected, and voila. But this time, first with v. 12 and once it did not work then with v. 11, it is different. Port 50000 was taken, so the installer put 50001 into the services file. When I try to connect locally with absolutely any user, I get this error: CLPPlus: Version 1.6 Copyright (c) 2009, 2011, IBM CORPORATION. All rights reserved. SQL> connect Enter DATABASE NAME [SAMPLE]: Enter HOSTNAME [localhost]: Enter PORT : 50001 Enter ID: db2admin Enter password: *********************** DB2 SQL Error: SQLCODE=-1031, SQLSTATE=58031, SQLERRMC=null, DRIVER=4.28.11 I added Administrator and myself to DB2ADMINS group, and it throws the same error for all of these accounts: db2admin, Administrator, and myself. The db2dec.lic license is applied: C:\Program Files\IBM\SQLLIB\BIN>db2licm.exe -l Product name: "DB2 Community Edition" License type: "Community" Expiry date: "Permanent" Product identifier: "db2dec" Version information: "11.5" Max amount of memory (GB): "16" Max number of cores: "4" TCP communication seems to be enabled: C:\Program Files\IBM\SQLLIB\BIN>db2set.exe -all [e] DB2PATH=C:\Program Files\IBM\SQLLIB [i] DB2INSTOWNER=DB212 [i] DB2PORTRANGE=60000:60005 [i] DB2INSTPROF=C:\PROGRAMDATA\IBM\DB2\DB2COPY0115 [i] DB2COMM=TCPIP [g] DB2_EXTSECURITY=YES [g] DB2_COMMON_APP_DATA_PATH=C:\ProgramData [g] DB2SYSTEM=DB212 [g] DB2PATH=C:\Program Files\IBM\SQLLIB [g] DB2INSTDEF=DB2 [g] DB2ADMINSERVER=DB2DAS00 DB2 is listening on 50001: TCP 0.0.0.0:50001 DB212:0 LISTENING 3280 [db2syscs.exe] Holes were drilled in the firewall, to allow unrestricted inbound access on ports 50001 and 60000-60005 that DB2 took in services. I can telnet into this server on port 50001 but not connect a DB2 client. **EDIT:** This morning, I freed port 50000, uninstalled 11.5, and further downgraded to 10.5 but that did not change anything. I then ran this in Administrator CLP: > db2 list db directory SQL1031N The database directory cannot be found on the indicated file system. SQLSTATE=58031 C:\Program Files\IBM\SQLLIB\BIN>db2 create db sample DB20000I The CREATE DATABASE command completed successfully. C:\Program Files\IBM\SQLLIB\BIN>db2 list db directory System Database Directory Number of entries in the directory = 1 Database 1 entry: Database alias = SAMPLE Database name = SAMPLE Local database directory = C: Database release level = 10.00 Comment = Directory entry type = Indirect Catalog database partition number = 0 Alternate server hostname = Alternate server port number Then in CLPP: db2 => connect to sample Database Connection Information Database server = DB2/NT64 10.5.1 SQL authorization ID = ADMINIST... Local database alias = SAMPLE db2 => select * from sysibm.sysdummy1 IBMREQD ------- Y So, the gist is that on Windows Server, neither of 12, 11.5, or 10.5 creates the SAMPLE DB. Why?

I tried creating a procedure to adjust a sequence according to the max value in a column. To get the max value from the column and store it in a variable I ended up using a "dummy" cursor. I assume there is a simpler way, but I could not get any of them to work. Any suggestions on how to get rid of the cursor and still be able to use the max value in the alter statement? --#SET TERMINATOR @ CREATE OR REPLACE PROCEDURE Adjust_Sequence( IN p_table_schema VARCHAR(128), IN p_table_name VARCHAR(128), IN p_column_name VARCHAR(128), IN p_sequence_name VARCHAR(128) ) LANGUAGE SQL BEGIN DECLARE v_max_value BIGINT DEFAULT 1; DECLARE v_sql_stmt VARCHAR(1000); DECLARE cur CURSOR FOR S; -- Construct SQL to get the max value of the specified column SET v_sql_stmt = 'SELECT COALESCE(MAX(' || p_column_name || '), 0) + 1 FROM ' || p_table_schema || '.' || p_table_name; -- Prepare and execute the statement properly PREPARE S FROM v_sql_stmt; OPEN cur; FETCH cur INTO v_max_value; CLOSE cur; -- Construct SQL to alter the sequence to restart with the new max value SET v_sql_stmt = 'ALTER SEQUENCE ' || p_sequence_name || ' RESTART WITH ' || v_max_value; -- Execute the sequence alteration EXECUTE IMMEDIATE v_sql_stmt; END @ --#SET TERMINATOR ;

On Red Hat 8.8 *Rocky Linux 8.8* I have Db2 v11.5.8.0 installed and database is working fine. 1. Today I downloaded v11.5.9.0 and unpacked the tar file. 2. I executed db2prereqcheck and all prerequisites are met. Successful. 3. Installed Db2 using db2_install command. Successful. 4. Upgraded instance with db2iupdt command. Successful. 5. Then I tried to start up an instance with db2start and error: *db2start: error while loading shared libraries: libaws-cpp-sdk-transfer.so: cannot open shared object file: No such file or directory* It looks some library is missing. I try to search the web, but can't find how to install this library. Any idea?

DB2 11.5.7.0 on Linux We've got a cron job that does: cd ${ARCH_LOC} tar -czvf /path/to-backups/${DB}_ARCHIVELOG_${NOW}.tar.gz S*.LOG && rm -vf S*.LOG >> ${SLOG} ISTM that there's a flaw her, since any archived log files written to $ARCH_LOC while the tar is running will get rmed but not saved. Thus, is there any way to suspend the **archiving** of log files, and then resume it after the tar ... && rm completes?

DB2 UDB 11.5.7.0 $ db2 get db cfg for PRPT01D | grep LOGARCH First log archive method (LOGARCHMETH1) = DISK:/udb/alogs/ Archive compression for logarchmeth1 (LOGARCHCOMPR1) = OFF Options for logarchmeth1 (LOGARCHOPT1) = Second log archive method (LOGARCHMETH2) = OFF Archive compression for logarchmeth2 (LOGARCHCOMPR2) = OFF Options for logarchmeth2 (LOGARCHOPT2) = $ db2 list history backup all for PRPT01D | head List History File for PRPT01D Number of matching file entries = 1 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID -- --- ------------------ ---- --- ------------ ------------ -------------- B D 20241006050916001 N D S0002434.LOG S0002434.LOG ---------------------------------------------------------------------------- $ db2 "prune logfile prior to S0002323.LOG" DB20000I The PRUNE command completed successfully. Even after running that command, old log files are still in /udb/alogs/udbinst/PRPT01D/NODE0000/LOGSTREAM0000/C0000000. Am I fundamentally misunderstanding something? Can I just rm those archived log files?

I noticed that importing data that truncates, only raises a warning. T thought it would be simple to just catch that warning and signal an error via a handler, but I don't seem to be able to do that. Any suggestions? CREATE OR REPLACE PROCEDURE FILE_IMPORT_HANDLER(filename VARCHAR(255), tablename VARCHAR(255)) LANGUAGE SQL BEGIN DECLARE EXIT HANDLER FOR SQLSTATE '22001' SIGNAL SQLSTATE '75002' SET MESSAGE_TEXT = '...'; CALL ADMIN_CMD('import from ' || filename || ' of del insert into ' || tablename); END @ I would like to fail hard if 22001 is encountered. In a sense, raise the severity for data truncation during the import

Before I realized that there existed a procedure for revalidating objects (SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS), I wrote my own. Now, I try to replace that with the standard one, but it appears as if the call to SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS destroys the cursor I'm looping over. Example of old procedure: db2 -td@ +c "BEGIN FOR v AS c1 CURSOR FOR SELECT SCHEMANAME FROM NYA.VALIDATION_SCHEMAS DO CALL TOOLBOX.COMPILE_SCHEMA2(v.schemaname); END FOR; END @" DB20000I The SQL command completed successfully. Example using ADMISN_REVALIDATE_DB_OBJECTS db2 "CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS(object_schema=>'TMP')" Return Status = 0 db2 "CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS(object_schema=>'TMP')" Return Status = 0 Trying to use that in a loop over a cursor: db2 -td@ +c "BEGIN FOR v AS c1 CURSOR FOR SELECT SCHEMANAME FROM NYA.VALIDATION_SCHEMAS DO CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS(object_schema=>v.schemaname); END FOR; END @" DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0501N The cursor specified in a FETCH statement or CLOSE statement is not open or a cursor variable in a cursor scalar function reference is not open. SQLSTATE=24501 Note that it does not help to hard wire the schema: db2 -td@ +c "BEGIN FOR v AS c1 CURSOR FOR SELECT SCHEMANAME FROM NYA.VALIDATION_SCHEMAS DO CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS(object_schema=>'TMP') [...] also yields an error. I tried variations on looping over a cursor: BEGIN DECLARE s VARCHAR(128); DECLARE v_at_end INTEGER default 0; DECLARE not_found CONDITION FOR SQLSTATE '02000'; DECLARE C1 CURSOR for SELECT SCHEMANAME FROM NYA.VALIDATION_SCHEMAS; DECLARE CONTINUE HANDLER FOR not_found SET v_at_end = 1 ; OPEN C1; fetch_loop: LOOP FETCH FROM C1 INTO s; IF v_at_end 0 THEN LEAVE fetch_loop; END IF; CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS(object_schema=>s); END LOOP; CLOSE C1; END @ But it does not work either. Any clue, what needs to be done to be able to call ADMIN_REVALIDATE_DB_OBJECTS in a loop?

I made a peculiar observation regarding external functions written in java which I hope someone can shed a light on. I tried it with several functions, but I'll use one of them as an example here: CREATE FUNCTION NYA.REMOVE_DIACRITICS( S VARCHAR(100)) RETURNS varchar(100) FENCED THREADSAFE DETERMINISTIC NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA RETURNS NULL ON NULL INPUT EXTERNAL NAME 'StringUtil:se.uhr.nya.commons.db.procedures.DiacriticUtil!removeDiacritics' NO EXTERNAL ACTION So, if I call the function outside the cte everything works as expected: with t(n) as ( values 65 union all select n+1 from t where n+1 < 70 ) select n, nya.remove_diacritics(chr(n)) from t SQL0347W The recursive common table expression "DB2INST1.T" may contain an infinite loop. SQLSTATE=01605 65 A 66 B 67 C 68 D 69 E 5 record(s) selected with 1 warning messages printed. But if I move the function call inside the cte, it is terminated immediately: with t(n,s) as ( values (65,nya.remove_diacritics(chr(65))) union all select n+1, nya.remove_diacritics(chr(n+1)) from t where n+1 < 70 ) select n,s from t SQL0347W The recursive common table expression "DB2INST1.T" may contain an infinite loop. SQLSTATE=01605 65 A 1 record(s) selected with 1 warning messages printed. If I replace the function call with a constant in the recursive leg of the cte, once again 5 rows are returned so I guess the recursion is terminated in that part: with t(n,s) as ( values (65,nya.remove_diacritics(chr(65))) union all select n+1, 'A' from t where n+1 < 70 ) select n,s from t ... 5 record(s) selected with 1 warning messages printed. FWIW, it is possible to trick the compiler by using a case statement and a non-obvious contradiction: with t(n,s) as ( values (65,nya.remove_diacritics(chr(65))) union all select n+1, case when n < 1000 then nya.remove_diacritics(chr(n+1)) else 'A' end from t where n+1 < 70 ) select n,s from t 65 A 66 B 67 C 68 D 69 E Is it any property on the function that can explain this behaviour? As mentioned I tried other functions as well, and they behave the same way. This is not a problem per see, I'm only curious about why this happens. If there is some rationale behind it, it is nice to know for future use.

How do I determine the default isolation level for our 11.5 DB2 instance running on Linux? I see there is a "special register" from [this IBM documentation](https://www.ibm.com/docs/en/db2/11.5?topic=registers-current-isolation) , however I'm not a DB2 expert so I'm not sure where to look for that, or how to "see" values for "special" registers. I also looked at [this](https://www.ibm.com/docs/en/db2/11.5?topic=mpf-mon-get-connection-details-table-function-get-connection-metrics-as-xml-document) , but I am having no luck understanding how to obtain the current isolation from that either.

I have installed fix pack 11.5.8 for DB2 version 11.5 in Windows server 2016. Now this fix pack is not compatible with application. I have to downgrade to 11.5.7. How to roll back the fix pack (11.5.8) in windows server?

I think I have seen somewhere that there is some sort of semantic difference between CHAR(n) FOR BIT DATA and BINARY(n), but I'm unable to locate where it was. Any pointers? I'm asking because I have loads of columns defined as CHAR(16) FOR BIT DATA from before BINARY(16) existed, and thinking about gradually migrate them to BINARY(16). I'm curious whether there are any circomstances where migrating one side of a f.k. relationship before the other will cause problems.

I'm looking into converting a number of log tables to range partitioning, main purpose is to reduce the size of the database by detaching and archiving historical data. Process looks like: CALL SYSPROC.ADMIN_MOVE_TABLE('S','T1','', '', '', '', '', '(ACTION_TIME) (STARTING FROM (''2000-01-01-00.00.00.000000'') ENDING AT (''2029-12-31-23.59.59.999999'') EVERY 1 YEAR)', '', 'COPY_USE_LOAD', 'MOVE' ); ... CALL SYSPROC.ADMIN_MOVE_TABLE('S','T120','', '', '', '', '', '(ACTION_TIME) (STARTING FROM (''2000-01-01-00.00.00.000000'') ENDING AT (''2029-12-31-23.59.59.999999'') EVERY 1 YEAR)', '', 'COPY_USE_LOAD', 'MOVE' ); For simplicity reasons during this test, I did not do anything about the index on each table (p.k. + ts), they remain in the same tablespace as before. All tables use this tablespace for their indexes Seems to work alright, although I get a number of mysterious warnings. For every table I get: SQL0206N "STATSPROFTYPE" is not valid in the context where it is used. and for some of the temporary tables (which seemed to be tables with quoted names ending with lowercase letters), I got other complaints about runstats. Did some tests and the tables seemed accessible. Then I detached old partitions like: for t in $(db2 -x "select rtrim(tabschema) || '.' || rtrim(tabname) from SYSCAT.DATAPARTITIONS where tabname like '%_LOG' group by tabschema, tabname having COUNT(datapartitionname) > 1"); do for p in part{0..18}; do db2 "alter table $t detach partition $p into ${t}_$p"; db2 "drop table ${t}_$p"; done # db2 "reorg table $t"; db2 "runstats on table $t with distribution and sampled detailed indexes all" done Still all tables are accessible, but if I query: db2 "SELECT substr(tabschema,1,20), substr(tabname,1,60), substr(VALUE,1,10) FROM SYSTOOLS.ADMIN_MOVE_TABLE WHERE KEY='STATUS'" All tables have a value of CLEANUP. I left it for a couple of hours, there are no activity in the database, but the tables remain the same. On average I probably removed 50% of the rows in these tables, but the size according to: db2 "CALL GET_DBSIZE_INFO(?, ?, ?, -1)" ... Parameter Name : DATABASESIZE Parameter Value : 769891516416 is not reduced. Due to a lack of ideas, I looped over the tables and reorged each table and all indexes, but it does not seem to change anything. Is it normal that tables stay in this state for a long period of time? Would it help to wait for CLEANUP, before migrating the next table?

On Linux/x86-64 I have Db2 v11.1 installed. When I tried to install Db2 v11.5 alongside existing Db2 installation I got error "*DBI1004W /usr/lib/libdb2.so detected*". I checked and I see symbolic links on paths: ls -l /usr/lib | grep "/opt/ibm/db2/" ls -l /usr/include | grep "/opt/ibm/db2" In error description is suggestion to delete those links. After using db2rmln command those links are removed. Installation of Db2 v11.5 was now successful. But I have checked and those symbolic links are not recreated. According to https://www.ibm.com/docs/en/db2/11.5?topic=servers-multiple-db2-copies-one-system-linux-unix this works as intended. Info: "*Links must not be created when multiple Db2 copies are intended to coexist.*" It is so fine those symbolic links do not exist if multiple copies of Db2 resist on the same Linux. My question is, what is purpose of those symbolic links and can I expect some problems not having them any more?

I'm playing around with docker and db2 but I'm getting into trouble when I try to execute commands as user db2inst1 into a running container. I start the container as (it is 1 line but I split it up for readability): docker run -itd --name mydb2 --privileged=true -p 50000:50000 -e LICENSE=accept -e DB2INST1_PASSWORD=pelle_paltnacke --mount type=volume,dst=${backupdir},volume-driver=local,volume-opt=type=nfs,\"volume-opt=o=nfsvers=4,addr=${addr}\",volume-opt=device=:${device} -v /etc/passwd:/etc/passwd -v /etc/group:/etc/group -v /opt/nya/users/db2inst1:/opt/nya/users/db2inst1 -v /home/system/db2fenc1/:/home/system/db2fenc1/ ibmcom/db2 Now, if I try to do: docker exec --user db2inst1 -ti mydb2 bash -c "cat /etc/passwd | grep db2inst1" unable to find user db2inst1: no matching entries in passwd file As root there is no problem: docker exec -ti mydb2 bash -c "cat /etc/passwd | grep db2inst1" db2inst1:x:422:422:DB2 Instance Administrator 1:/opt/nya/users/db2inst1:/bin/bash and also --user root works fine: docker exec --user root -ti mydb2 bash -c "cat /etc/passwd | grep db2inst1" db2inst1:x:422:422:DB2 Instance Administrator 1:/opt/nya/users/db2inst1:/bin/bash So I tried with the uid from the mounted passwd file: docker exec --user 422 -ti mydb2 bash -c "cat /etc/passwd | grep db2inst1" db2inst1:x:422:422:DB2 Instance Administrator 1:/opt/nya/users/db2inst1:/bin/bash /etc/passwd is readable for everyone. Anyhow, using the uid does not get me far: docker exec --user 422 -ti mydb2 bash -c "db2licm -l" bash: db2licm: command not found so I try with: docker exec --user 422 -ti mydb2 bash -c "whoami; . ~db2inst1/sqllib/db2profile; db2licm -l" db2inst1 bash: /opt/nya/users/db2inst1/sqllib/adm/db2licm: Permission denied This is just a couples of commands I ran to demonstrate the problem. Does anyone have an explanation as to why the --user db2inst1 is not able to execute them? FWIW, I tried without the nfs-mount but I get the same behaviour. The container itself seems to be working alright. If I spin up the container as above and: #> docker exec -ti mydb2 bash [root@0ee67959246f /]# mkdir -p /data/db/db2 [root@0ee67959246f /]# chown db2inst1:db2iadm1 /data/db/db2/ [root@0ee67959246f /]# su - db2inst1 [db2inst1@0ee67959246f ~]$ cd /data/backup/db2/wb11/MD000I11/ [db2inst1@0ee67959246f MD000I11]$ db2 "restore db MD000I11 incremental auto taken at 20220307141244 to /data/db/db2 into WD000I11" DB20000I The RESTORE DATABASE command completed successfully. EDIT: An interesting observation is: docker exec --user 422 -ti mydb2 bash -c "id" uid=422(db2inst1) gid=0(root) groups=0(root) docker exec --user 422:422 -ti mydb2 bash -c "id" uid=422(db2inst1) gid=422(db2iadm1) groups=422(db2iadm1) docker exec --user 422:422 -ti mydb2 bash -c "whoami; . ~db2inst1/sqllib/db2profile; db2licm -l" db2inst1 Product name: "DB2 Community Edition" License type: "Community" ... Unfortunate: docker exec --user db2inst1:db2iadm1 -ti mydb2 bash -c "id" unable to find user db2inst1: no matching entries in passwd file

I must admit I have not given it much thought previously, but I always assumed that a NOT DETERMINISTIC function would not be reused between function calls, Example: CREATE OR REPLACE FUNCTION TEST1() RETURNS INT CONTAINS SQL NO EXTERNAL ACTION NOT DETERMINISTIC RETURN VALUES INT(1000*RAND()) @ But if I try that as: db2 "select test1() from ( values (1),(2) )" 1 ----------- 868 868 I get the same value for both 1 and 2. I assume this is because test1() is evaluated once and the result is reused for both 1 and 2. I thought not deterministic would prevent this, but apparently, I was wrong. If I instead create the function as: CREATE OR REPLACE FUNCTION TEST2() RETURNS INT CONTAINS SQL NO EXTERNAL ACTION NOT DETERMINISTIC BEGIN ATOMIC RETURN VALUES INT(1000*RAND()); END @ db2 "select test2() from ( values (1),(2) )" 1 ----------- 596 16 It behaves as I would have expected. Any links to docs that can shed some light on this? EDIT: Yet another simplified scenario, but closer to the real deal CREATE TABLE T ( X INT NOT NULL PRIMARY KEY ) @ CREATE OR REPLACE FUNCTION TEST3() RETURNS INT READS SQL DATA NO EXTERNAL ACTION NOT DETERMINISTIC RETURN WITH TT (X,N) AS ( VALUES (INT(1000*RAND()),0) UNION ALL SELECT INT(1000*RAND()), N+1 FROM TT, T WHERE N < 10000 AND T.X = TT.X ) SELECT X FROM TT WHERE N = (SELECT MAX(N) FROM TT) @ CREATE OR REPLACE FUNCTION TEST4() RETURNS INT READS SQL DATA NO EXTERNAL ACTION NOT DETERMINISTIC BEGIN ATOMIC RETURN WITH TT (X,N) AS ( VALUES (INT(1000*RAND()),0) UNION ALL SELECT INT(1000*RAND()), N+1 FROM TT, T WHERE N < 10000 AND T.X = TT.X ) SELECT X FROM TT WHERE N = (SELECT MAX(N) FROM TT); END @ db2 "select test3() from ( values (1),(2) )" 1 ----------- 320 320 db2 "select test4() from ( values (1),(2) )" 1 ----------- 846 836 I don't mind using BEGIN ATOMIC in my function definition, I'm mostly curious why db2 reuses the value from a not deterministic function EDIT2: From the plan it appears as if test3 is inlined (that I would have expected from a deterministic function without a begin atomic block) Optimized Statement: ------------------- SELECT (SELECT Q11.$C0 FROM (SELECT MAX(Q9.$C0) FROM (SELECT Q8.$C1 FROM (SELECT INT((+1.00000000000000E+003 * RAND())), (Q4.$C1 + 1) FROM DB2INST1.T AS Q3, Q8 AS Q4 WHERE (Q3.X = Q4.$C0) AND (Q4.$C1 < 10000) UNION ALL SELECT INT((+1.00000000000000E+003 * RAND())), 0 FROM (VALUES 1) AS Q6 ) AS Q8 WHERE Q8.$C1 IS NOT NULL ) AS Q9 ) AS Q10, Q8 AS Q11 WHERE (Q11.$C1 = Q10.$C0) ) FROM (SELECT $INTERNAL_FUNC$() FROM (VALUES 1, 2) AS Q1 ) AS Q2

What is the easiest way to **copy a single database** (has no large objects) from **DB2 10.5 Express-C** instance to **DB2 11.5 Community Edition** instance? I tried [this method](https://dba.stackexchange.com/a/214223/225182) but obviously db2 restore database is meant to be used only with the originating instance because the original database path is referenced in the error message: SQL1051N The path "/var/lib/db/db2/db2inst1" does not exist or is not valid.

I'm running DB2 11.5 and want to grant select, insert, update or delete rights for all tables in a specific schema. SELECTINAUTH etc. seems to be perfect for this. When I'm looking to the SYSCAT.SCHEMAAUTH Catalog View I can see the following columns: - ALTERINAUTH - CREATEINAUTH - DROPINAUTH - SELECTINAUTH - INSERTINAUTH - UPDATEINAUTH - DELETEINAUTH The first 3 privileges can be granted by e.g. GRANT ALTERINAUTH ON SCHEMA TO USER . But this don't work for the last 4 columns. The Knowledge Center tells that this are implicit privileges, but I can't find any hint, how a user or a group can get this privileges implicit or explicit. Does anybody know when or how e.g. INYSERTINAUT is set to Y?

I'm working on DB2 stored procedure, which will be called by the Korn Shell (.ksh). My DB2 version is 11.5 and it is running on AIX. I was stuck in TIMESTAMP to DATE conversion. Below is my requirement. If s_day < day(s_date) then start_date = YYYY-MM from s_date + 1 day from s_day end if Note here s_date is DATE variable, s_day is CHAR variable and start_date is DATE variable. So I tried, the following code, but got an error. IF s_day < DAY(s_date) THEN SET start_date = DATE (DATE(s_date,'YYYY-MM') || s_day + 1); END IF Sample values s_day =03 s_date= 15/05/2020 expected output start_date is 2020-05-04 Can anyone help me to achieve expected output?