SPFile in Oracle

SPFile stands for Server Parameter File.  This was introduced in Oracle 9i and it is different than the normal init<sid>.ora file.  It is a binary file and not a text based file and is stored on the server rather than on the client machine.  The default location of this file is under $ORACLE_HOME/dbs directory.  It can be used to maintain the changes to the parameter values over the shutdown or startup of an instance and that is what makes it useful.  Basically any changes that we make to the configuration parameter using the ALTER SYSTEM statement get saved to the permanent configuration file.  If you want, you can also make dynamic changes to the parameters without making them a part of the SPFILE - you can use the SCOPE clause in order to make that happen - here is how the syntax looks like:

ALTER SYSTEM SET your_parameter_name_here = your_parameter_value_here

SCOPE = {MEMORY | SPFILE | BOTH};

You can also migrate the parameters from an existing init<sid>.ora file to SPFILE by using this command:

CREATE SPFILE FROM PFILE = ‘your_path_goes_here/init<sid>.ora’;

And do note that you should not directly update the SPFILE contents.  Use the ALTER SYSTEM command to do so.

Understanding the recovery models

At many client sites, we have seen that even in the production environment, some of the DBAs/Database Developers run their databases in the Simple Recovery Model - and all these are mission critical databases in which you need zero data loss and a point in time recovery is always needed. When asked why you are running it in the Simple Recovery Model, the answer given is that the transaction log just kept on growing so we changed the recovery model to keep it in check. One needs to understand the different recovery models that are available in SQL Server and also how to use transaction log back-ups in order to keep the growth of the transaction logs in check. Here is a small description of the different recovery models available in SQL Server:

Full Recovery Model: As the name suggests, it is a complete recovery model and allows us to recover all of the data to any point in time as long as all the required backup files that we have are available and usable. All the operations against a database that uses a full recovery model are fully logged. As mentioned above, when you are running under a full recovery model, make sure that you have implemented a transaction log backup scheme as well so that you can keep the log growth in check. Production databases which are mission critical and cannot have any data loss need to be always run in this recovery model. Also, if you are using database mirroring, then you would use this recovery model. When you are using Full recovery model, you can do all types of backups: full backups, differential backups, file/filegroup backups, partial backups, copy only backups as well as transaction log backups.

Bulk Logged Recovery Model: This is a step down from the Full recovery model and as the name suggests, it can be used when we do not want to pay the price of bulk operations (bulk insert, select into…, create index, bcp etc.) taking up space in the transaction log. Databases running under this recovery model do not fully log such bulk operations. So, the transaction log does not get filled up by the bulk operations and we can still do point in time recovery but those bulk operations are lost. So, essentially in environments where one does not do bulk operations, this works the same way as the Full recovery model. And just like the full recovery model, you need to backup your transaction logs else it will keep on growing. All type of backups that can be done with the full recovery model can be done with bulk recovery model as well.

Simple Recovery Model: Simple recovery model is more suited in those scenarios where your data is not that critical and some data loss is acceptable. It allows you to do a simple full backup or a differential backup but not transaction log backups. Any work done since the last backup can be lost. Typically, development or test databases or databases where the data is mostly static or if the data can be re-created again by loading up the data again are scenarios where you might want to use it but never in a mission critical transactional database where point in time recovery is needed. All the backups other than the transaction log backups can be taken for databases that are under this recovery model.

We hope that this gives a very brief but good understanding of the differences between the three recovery models present in SQL Server. You can either change the recovery models from SSMS (SQL Server Management Studio). Right click the database and choose properties and then when you click on options, you will see a drop down for the Recovery model under which you can select which one you want. Alternate way of doing this is through the command line using the ALTER DATABASE command:

ALTER DATABASE <DATABASE_NAME_HERE> SET RECOVERY <FULL OR BULK_LOGGED OR SIMPLE>

Put your database name above (and remove the <> braces and likewise choose your recovery model and remove the <> braces).

Finding top memory objects in SQL Server 2005

While troubleshooting a memory related performance issue at a client site, my colleague pointed out a query that can be used to find the top x memory consumers.  One can make use of sys.dm_os_memory_clerks and sys.dm_os_memory_objects DMVs to retrieve that information.  Here is the SQL:

select distinct pages_allocated_count, type
from sys.dm_os_memory_objects as domo
inner join (select top 20 page_allocator_address
from sys.dm_os_memory_clerks
order by multi_pages_kb desc) as IV
on domo.page_allocator_address = IV.page_allocator_address
order by pages_allocated_count desc

In the case of this particular client, we saw that the CLR’s memory clerk was the top culprit.  There was no need to make use of the CLR for what the client was trying to do.  We re-wrote that functionality as a simple stored procedure and removed the CLR usage to ease the memory pressure.

Windows 7 and Multi-Touch

By now, you must have heard about Microsoft Surface - we had also covered it before in one of our posts. If you haven’t looked at it, here is the link. Some of those multi-touch innovations are making it’s way to the next version of Windows - Windows 7. All things digital had posted an interview of Bill Gates and Steve Ballmer and it has a video that demonstrates the multi-touch capabilities of Windows 7.  It’s a very interesting demo and a very good chat with Mr. Gates and Mr. Ballmer.  Interesting times are ahead…

Enhancements to the OUTPUT clause in SQL Server 2008

We had covered the OUTPUT and the OUTPUT INTO clauses in SQL Server 2005 before in this post. Prior to SQL Server 2008, as you can see from the examples from that post, one could not filter out the records that are returned using the OUTPUT/OUTPUT INTO clauses i.e. one can choose only the different columns that one wants in those clauses but cannot choose which rows one wants to return. As a result, one has to dump all of that data into a table and then apply a filter criteria (WHERE clause) at that table in order to sub-select the records that one wants to get.

In SQL Server 2008, one of the new T-SQL enhancements makes this pretty simple. One can now define a table expression based off a modification statement with an OUTPUT clause and then apply the filters on it. For Example:

INSERT INTO dbo.PO_DTL (….)
SELECT ….
FROM (UPDATE dbo.INPT_PO_DTL
SET Quantity *= 1.5
OUTPUT
inserted.PO_DTL_ID,
deleted.Quantity AS Old_Qty,
inserted.Quantity AS New_Qty
WHERE RECVD_DATE > getdate() - 10) AS IV
WHERE IV.Old_Qty < 100.0 AND New_Qty >= 100.0
GO

One can also join that data set with another table and do processing.  The biggest advantage (besides not having another table and another set of steps to achieve the same thing is the fact that we are reducing the number of records that we have to retrieve - previously, if the data set returned would have been large, we would have first stored it in a temp table and then applied a filter on it.  If that data set was huge, it would have had performance implications.  With this new feature, we can easily reduce that overhead and get only the records that we want/need.

You can also see the new syntax of ” *= ” for the multiplication of the data - that is the compound assignment operator in SQL Server 2008.

Database Snapshots in SQL Server 2005

Database snapshots was introduced as a new feature in SQL Server 2005. It is available only in Enterprise Edition and is used for creating a read only copy of a database as of a certain point in time. There are a lot of benefits of having such a database snapshot scheme in place - two big benefits being reporting applications can use this snapshot instead of the regular OLTP/OLAP databases, and the other one being that you can use it for recovery purposes. There are other benefits as well that we will cover in this post. First, let’s see how we can go about creating these database snapshots.

First most important thing to understand is that database snapshots manage the data at the page level. SQL Server does not create another database file when we create a database snapshot, instead it creates what is called as a sparse file. We specify the name and location of the sparse file when we first create the snapshot
and this file initially has no data in it and is allocated minimal disk space. As and when a page is modified in the source database, the original version of the page file gets written to this sparse file. This process is also called as “copy-on-write”. Having this process in place allows us to create a record of the database as it existed when the sparse file was first created. Key thing to note is that the un-changed pages remain in the source database itself. Another thing to deduce from that point is that when/if we do a database recovery using the sparse file, the original source database needs to be present since the un-changed pages are not present in the sparse file.

So, the sequence of events is this:

1) When the snapshot is first created, the sparse file is empty.

2) When a change (update) happens in the source database, the original page is then written out to the sparse file.

So, any read operations that are done on the snapshot are:

a) Read from the source if the data has not changed since the snapshot was taken,

b) Read from the source as well as the sparse file (for the data that has been updated at the source). As mentioned above in #2, when the update happens at the source, the original data page gets copied over to the sparse file.

Another key thing to note is that since the database snapshots are NOT redundant storage, having snapshots does not protect us from any database corruption at the source database. Tomorrow, we will go through the commands to cover how to set up database snapshots in SQL Server 2005 as well as to go through a couple of different scenarios - locking, heavy IO operations and the advantages and limitations of this feature.

Bitmap Indexes and Bitmap Filters

While troubleshooting an issue yesterday at a client site, a question came up about bitmap indexes vs the bitmap filters. The two are not the same and it is important to understand the differences between the two. Bitmap indexes, as the name suggests, are physical structures that persist to disk and like other indexes are used for retrieval of the data whereas bitmap filters are memory resident and are used for enhancing the performance of the query at runtime. SQL Server 2005 does not have any bitmap indexes like Oracle does. You can read more on Oracle’s bitmap indexes here. SQL Server on the other hand, takes care of this internally by using bitmap filters. It does not require hints or metadata and there is no additional storage costs since there are no indexes.

When you see the execution plans in SQL Server, you will sometimes see the operator bitmap filter and it confuses people who are used to working in both Oracle and SQL Server. Bitmap filters are used only in parallel execution plans (not serial). It helps in improving the performance of the query by doing a semijoin reduction early in the query execution. Bitmap filters are typically used when the optimizer decides to do a Hash Join or a Merge Join (refer to the different join methods here). Bitmap filters are very useful in optimization of data warehouse queries. The star join query optimizations use bitmap filters which essentially means that it performs a semi-join reduction i.e. only the rows in the second table that qualify for the join in the first table are processed. SQL Server 2008 has even more improvements in this area. While in SQL Server 2005, the bitmap filter was applied to only one join, in SQL Server 2008, multiple bitmap filters are allowed over the same fact table. In addition, in SQL Server 2008, the bitmap filters can be moved and re-ordered dynamically based on the selectivity.

You can read more on bitmap filtering at the MSDN post here.

SQL Server Audit in SQL Server 2008

SQL Server 2005 already has a good set of auditing capabilities namely C2 Audit Mode Option, DDL Triggers and SQL Server Profiler (besides writing one’s own DML triggers). In SQL Server 2008, one can make use of CDC (Change Data Capture) for capturing the changes as well as a new feature called SQL Server Audit.

SQL Server Audit is built at the top of XEVENT which stands for Extended Events which is a new event infrastructure that has been introduced in SQL Server 2008.

SQL Server Audit involves these steps:

a) We first need to create a SQL Server Audit object which is used to collect the instance or database level actions. The results of the audit are sent to a target which can be a file, windows application event log or security event log and can be viewed via the event viewer.

b) We need to create a database audit specification or a server (for an instance) audit specification which needs to be mapped to the audit object created in Step #(a). This specification collects the pre-defined groups of atomic events and sends them to the Audit Object and that records them in the target (file, application or security event logs).

c) Then we need to enable the SQL Server Audit and the Audit Specification.

d) We can then read the logged data using either event viewer (start/run/eventvwr) or using the Log File Viewer or a new function: fn_read_audit_file.

You can read more on SQL Server Audit at this technet link here.

Recovering a table using Flashback Table command

Prior to Oracle 10g, it was not possible to recover the table back once it is dropped. Oracle introduced new feature ‘Oracle Flashback Table’, one of the many useful features. Using this new feature we can recover a dropped table to its earlier state with single statement. Let us see how we can achieve it.

First let us create an empty table and populate it with some data. If you already have table with the same name, change all the occurrences of the table name with some other name.

CREATE TABLE TEST
(
TEST_ID      NUMBER(9) NOT NULL,
TEST_DATE    DATE,
TEST_INDIC   NUMBER(1) DEFAULT 0 NOT NULL,
CONSTRAINT PK_TEST PRIMARY KEY(TEST_ID)
)
– TABLESPACE Clause
/

CREATE SEQUENCE TEST_SEQ
START WITH 1
CACHE 100
/

CREATE        INDEX TEST_IND_1 ON TEST(TEST_DATE)
– TABLESPACE CLAUSE
/

CREATE OR REPLACE TRIGGER TIB_TEST
BEFORE INSERT ON TEST FOR EACH ROW
BEGIN
IF :NEW.TEST_ID IS NULL THEN
SELECT TEST_SEQ.NEXTVAL INTO :NEW.TEST_ID
FROM DUAL;
END IF;
END;
/

INSERT INTO Test(test_date) VALUES(sysdate);
INSERT INTO Test(test_date) VALUES(sysdate);

In order to recover table back, we need to make sure that recyclebin parameter is on. Run following SQL to see the current value of parameter.

SQL>show parameter recyclebin

NAME                                 TYPE        VALUE
———————————— ———– ——————————
recyclebin                           string      OFF

Recyclebin parameter value is set to off, so we need to turn it on first. We can do it either at session level or at system level. It is a dynamic parameter so there is no need to stop and start the database after changing its value. We will set it at a system level. Alternatively we can put it in the init<sid>.ora file as well.

SQL> ALTER SYSTEM SET recyclebin = ON;

Flashing back to before drop

Before drop syntax allows to restore the table in a state just before the table was dropped. We already created table previously. Let us check it first and then drop it.

SQL>select * from tab;

TNAME                          TABTYPE  CLUSTERID
—————————— ——- ———-
TEST                           TABLE

Now let us drop the table and then again run the same query.

SQL>drop table test;
Table dropped.
SQL>select * from tab;

TNAME                          TABTYPE  CLUSTERID
—————————— ——- ———-
BIN$77qGCfjISXWU+Zmy+f5GFQ==$0 TABLE

Even though we have dropped the table, table is not actually dropped. Instead it is renamed with system generated name and is kept in recyclebin. You can view details either from user_recyclebin view or recyclebin synonym. It also renames any indexes and triggers of the table. Below query shows the original name and new name of all the objects which were dropped.

SQL> select original_name, type, object_name from recyclebin;

ORIGINAL_N TYPE                      OBJECT_NAME
———- ————————- ——————————
TEST       TABLE                     BIN$77qGCfjISXWU+Zmy+f5GFQ==$0
PK_TEST    INDEX                     BIN$eYaVVVT7Q2eGx2qr1f+Nhg==$0
TEST_IND_1 INDEX                     BIN$ALBfM1WhRPKBrsAkaHmQNA==$0
TIB_TEST   TRIGGER                   BIN$8UeSfnDBQtuinSD2vUFF7g==$0

Now let us try to recover the table. Issue following command to revive the table.

SQL> flashback table test to before drop;
Flashback complete.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
—————————— ——- ———-
TEST                           TABLE

Table is now back entirely including trigger and indexes along with data. Flashback command renames the table to its original name but does not rename the indexes, triggers or constraints. We need to manually rename them. Get the object name and its original name from the query on the previous page.

SQL> alter trigger “BIN$8UeSfnDBQtuinSD2vUFF7g==$0″ rename to TIB_TEST;
Trigger altered.

SQL> alter index “BIN$eYaVVVT7Q2eGx2qr1f+Nhg==$0″ rename to PK_TEST;
Index altered.

SQL> alter index “BIN$ALBfM1WhRPKBrsAkaHmQNA==$0″ rename to TEST_IND_1;
Index altered.

Constraint names are not retrievable from user_recyclebin view so we have to refer to original scripts or any other documentation to get its original name but following is the syntax to rename the constraints.

SQL> alter table test rename constraint “BIN$2WvwhoEMTRiDYrEumwLPCg==$0″ to PK_TEST;

Flashing back to SCN (System change number)

Other use of flashback table is to replace the data in the table to a different point of time using timestamp and/or SCN clause. Here we will show you how we can achieve it using SCN.

Let us first get the current SCN value. Make sure that you have execute privilege on dbms_flashback package. Run following statement to get current SCN.

SQL>  select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
————————
3965449

At this point, data in the table is as shown below.

SQL> select test_id, test_indic from test;

TEST_ID TEST_INDIC
———- ———-
1          0
2          0

Now we will update value of test_indic column to 1.

SQL> update test
2     set test_indic = 1
3  /

2 rows updated.

SQL> commit;

Commit complete.

First we will compare the old value and new value of test_indic column by joining against table version at particular SCN. Run following SQL. Make sure to change the SCN value with correct value or you can parameterize the query to get the input of SCN number.

SQL> select a.test_id, a.test_indic new_indic, b.test_indic old_indic
2      from test a inner join test as of scn 3965449 b on b.test_id = a.test_id;

TEST_ID  NEW_INDIC  OLD_INDIC
———- ———- ———-
1          1          0
2          1          0

In above query, we are self joining the test table with one of its past version to compare the updated column values. Now let us see how we can restore the table to that SCN value. Issue following command to reinstate the copy of the table at SCN value.

SQL> flashback table test to scn 3965449;
flashback table test to scn 3965449
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled

Oops! We ran into an error. Whenever we are using flashback clause with SCN or TIMESTAMP, we need to enable the row movement for a table.

SQL> alter table test enable row movement;
Table altered.

Now issue the flashback statement again and review the data in the table.

SQL> flashback table test to scn 3965449;
Flashback complete.

SQL> select test_id, test_indic from test;
TEST_ID TEST_INDIC
———- ———-
1          0
2          0

We have successfully reverted back the changes and restored the table to a specific point in time. In similar fashion flashback clause can be used with timestamp and restore point options as well.

Extract Day, Month or Year from a Date Field

One of the developers at a client site was writing up a report for the yearly sales. Invoice data is stored as per date when the invoice was issued. He needed to find out a way to extract year from the date field and sum up the sales amount on yearly basis. There are many ways of doing it … in this post, we will show one of the most common and easy ways of achieving this.

Oracle uses an EXTRACT(datetime) function to extract the value of a specified datetime field from a datetime expression. Function is very handy to manipulate specific datetime field value like day, month or year. Following values can be extracted from the column having date datatype. Apart from values mentioned below, timezone related values like timezone_region, timezone_hour and timezone_minute can also be obtained using EXTRACT function if underlying datatype is TIMESTAMP WITH TIME ZONE.

* Day, Month, Year, Hour, Minute, Second

Create following table and populate it with the data. If you already have table with the same name, change all the occurrences of the table name with some other name.

CREATE TABLE Invoice
(
INVOICE_NUMBER      NUMBER(9) NOT NULL,
INVOICE_DATE          DATE NOT NULL,
CLIENT_ID                NUMBER(9)     NOT NULL,
INVOICE_AMT           NUMBER(9,2) DEFAULT 0 NOT NULL,
PAID_FLAG               NUMBER(1) DEFAULT 0 NOT NULL, — 0 Not paid/ 1 paid
CONSTRAINT PK_INVOICE PRIMARY KEY(INVOICE_NUMBER)
)
– TABLESPACE Clause
/

CREATE SEQUENCE INVOICE_SEQ
START WITH 1
CACHE 100
/

INSERT INTO INVOICE(Invoice_Number, Invoice_date, client_ID, Invoice_Amt)
VALUES(INVOICE_SEQ.NEXTVAL, sysdate-5,101,1100.00);

INSERT INTO INVOICE(Invoice_Number, Invoice_date, client_ID, Invoice_Amt)
VALUES(INVOICE_SEQ.NEXTVAL, sysdate-10,102,1100.00);

INSERT INTO INVOICE(Invoice_Number, Invoice_date, client_ID, Invoice_Amt)
VALUES(INVOICE_SEQ.NEXTVAL, sysdate-20,103,1100.00);

INSERT INTO INVOICE(Invoice_Number, Invoice_date, client_ID, Invoice_Amt)
VALUES(INVOICE_SEQ.NEXTVAL, sysdate-40,101,1100.00);

INSERT INTO INVOICE(Invoice_Number, Invoice_date, client_ID, Invoice_Amt)
VALUES(INVOICE_SEQ.NEXTVAL, sysdate-38,101,1500.00);

INSERT INTO INVOICE(Invoice_Number, Invoice_date, client_ID, Invoice_Amt)
VALUES(INVOICE_SEQ.NEXTVAL, sysdate-60,102,1100.00);

INSERT INTO INVOICE(Invoice_Number, Invoice_date, client_ID, Invoice_Amt)
VALUES(INVOICE_SEQ.NEXTVAL, sysdate-65,103,1100.00);

Run following SQL from SQL*Plus prompt. It Extracts Year from the Invoice_Date and group it by year to see yearly sales.

SQL> SELECT EXTRACT(Year FROM Invoice_date) as Year,
SUM(Invoice_Amt) as Sales_Amt
FROM Invoice
GROUP BY EXTRACT(Year FROM Invoice_Date);

YEAR  SALES_AMT
—- ———-
2006       4800
2007       3300

MS SQL Server has datepart functions. Using datepart we can get following datetime fields.

*  Day , Dayofyear, Week , Weekday, Month, Quarter, Year
* Hour, Minute, Second, Millisecond

It also has DAY, MONTH and YEAR functions which are equivalent to datepart(dd,date), datepart(mm,date) and datepart(yy,date) respectively. Let us create the table and see how we can do it.

CREATE TABLE dbo.Invoice
(
INVOICE_NUMBER      INT IDENTITY(1,1) NOT NULL,
INVOICE_DATE        DATETIME NOT NULL,
CLIENT_ID           INT    NOT NULL,
INVOICE_AMT         NUMERIC(9,2) DEFAULT 0 NOT NULL,
PAID_FLAG           TINYINT DEFAULT 0 NOT NULL, — 0 Not paid/ 1 paid
CONSTRAINT PK_INVOICE PRIMARY KEY(INVOICE_NUMBER)
)
– FILEGROUP Clause
GO

INSERT INTO INVOICE(Invoice_date, client_ID, Invoice_Amt) VALUES(getDate()-5,101,1100.00);
INSERT INTO INVOICE(Invoice_date, client_ID, Invoice_Amt) VALUES(getDate()-10,102,1100.00);
INSERT INTO INVOICE(Invoice_date, client_ID, Invoice_Amt) VALUES(getDate()-20,103,1100.00);
INSERT INTO INVOICE(Invoice_date, client_ID, Invoice_Amt) VALUES(getDate()-40,101,1100.00);
INSERT INTO INVOICE(Invoice_date, client_ID, Invoice_Amt) VALUES(getDate()-38,101,1500.00);
INSERT INTO INVOICE(Invoice_date, client_ID, Invoice_Amt) VALUES(getDate()-60,102,1100.00);
INSERT INTO INVOICE(Invoice_date, client_ID, Invoice_Amt) VALUES(getDate()-65,103,1100.00);

We can use either Year function or Datepart syntax to get the final results. Both SQLs are shown under. You can either run it from Query Analyzer or Management studio.

SELECT YEAR(Invoice_date) as Year, SUM(Invoice_Amt) as Sales_Amt
FROM Invoice
GROUP BY Year(Invoice_Date)
GO
OR
SELECT DATEPART(YY,Invoice_date) as Year, SUM(Invoice_Amt) as Sales_Amt
FROM Invoice
GROUP BY DATEPART(YY,Invoice_Date)
GO

DB2 LUW has date and timestamp datatype for storing dates. Given a date, time or timestamp value, we can extract following datetime fields.

* Day,  Month, Year (date and timestamp)
*  Hour, Minute, Second, MicroSecond (time and timestamp)

Apart from above functions, it also returns week, quarter, dayofweek  and dayofyear given a date or timestamp.

CREATE TABLE Invoice
(
INVOICE_NUMBER      INTEGER NOT NULL GENERATED BY DEFAULT AS
IDENTITY (START WITH +1, INCREMENT BY +1, CACHE 1000),
INVOICE_DATE        TIMESTAMP NOT NULL,
CLIENT_ID           INTEGER       NOT NULL,
INVOICE_AMT         DECIMAL(9,2) DEFAULT 0 NOT NULL,
PAID_FLAG           SMALLINT DEFAULT 0 NOT NULL,
CONSTRAINT PK_INVOICE PRIMARY KEY(INVOICE_NUMBER)
);

INSERT INTO INVOICE(Invoice_date, client_ID, Invoice_Amt) VALUES(current timestamp - 5 days,101,1100.00);
INSERT INTO INVOICE(Invoice_date, client_ID, Invoice_Amt) VALUES(current timestamp - 10 days,102,1100.00);
INSERT INTO INVOICE(Invoice_date, client_ID, Invoice_Amt) VALUES(current timestamp - 20 days,103,1100.00);
INSERT INTO INVOICE(Invoice_date, client_ID, Invoice_Amt) VALUES(current timestamp - 40 days,101,1100.00);
INSERT INTO INVOICE(Invoice_date, client_ID, Invoice_Amt) VALUES(current timestamp - 38 days,101,1500.00);
INSERT INTO INVOICE(Invoice_date, client_ID, Invoice_Amt) VALUES(current timestamp - 60 days,102,1100.00);
INSERT INTO INVOICE(Invoice_date, client_ID, Invoice_Amt) VALUES(current timestamp - 65 days,103,1100.00);

Following is the SQL to obtain yearly sales in DB2. You can run it from Command Editor.

SELECT YEAR(Invoice_date) as Year, SUM(Invoice_Amt) as Sales_Amt
FROM Invoice
GROUP BY Year(Invoice_Date);