Cara Cepat Menampilkan History Database Size

Seringkali, kita diminta secara mendadak untuk menampilkan history ukuran database dari waktu ke waktu,

idealnya kita harus menghitung ukuran per baris dari data yang ada. namun terdapat juga beberapa query yang dapat membantu kita tanpa melakukan hal tersebut , beberapa yang berhasil saya kumpulkan antara lain :

{ ORACLE } :


select to_char(creation_time, 'MM-RRRR') "Month", sum(bytes)/1024/1024/1024 "Growth in GB"
from sys.v_$datafile
where to_char(creation_time,'RRRR')='2014'
group by to_char(creation_time, 'MM-RRRR')
order by to_char(creation_time, 'MM-RRRR');

dengan query ini, kita akan mendapatkan ukuran database dari waktu ke waktu memanfaatkan v_$datafile , yang mana tentu syaratnya database anda belum di restart. jika sudah direstart maka v_$datafile pun akan kehilangan catatannya.

{SQL SERVER} :


SELECT
[database_name] AS "Database",
DATEPART(month,[backup_start_date]) AS "Month",
AVG([backup_size]/1024/1024) AS "Backup Size MB",
AVG([compressed_backup_size]/1024/1024) AS "Compressed Backup Size MB",
AVG([backup_size]/[compressed_backup_size]) AS "Compression Ratio"
FROM msdb.dbo.backupset
WHERE [database_name] = N'AdventureWorks'
AND [type] = 'D'
GROUP BY [database_name],DATEPART(mm,[backup_start_date]);

dengan query ini , kita memanfaatkan pencatatan backup yang dilakukan oleh SQL SERVER, tentunya ini hanya berlaku untuk database yang rutin di backup , jika tidak rutin ya wassalam.

ok sekian selamat berhitung

SQLServer Import Excel 2007 Error

setelah sekian lama, akhirnya saya mendapatkan error legendaris ini

Picture1

error ini muncul saat ingin mengimport excel 2017 ke sqlserver melalui SQLServer Import and Export Wizard

solusi yang dapat dilakukan adalah :

  1. menginstall https://www.microsoft.com/en-us/download/details.aspx?id=13255 , saya menggunakan yang versi 64 bit
  2. jika point 1 tidak berhasil , maka gunakan https://www.microsoft.com/en-us/download/confirmation.aspx?id=23734

saya pribadi banyak bisa setelah point 2 saya install saya baru berhasil menjalankan import excel 2017 dengan lancar sekali…

semoga membantu,

happy querying :p

Akses user SqlServer pada linked server

jika anda pernah mengalami kasus seperti :

image_thumb26

maka user yang anda pakai belum diberi grant untuk mengakses linked server ,

untuk mengatasi hal ini, diperlukan hanya 2 langkah saja :

Tambahkan user anda pada database master

USE [master]
GO
CREATE USER user_login_anda FOR LOGIN user_login_anda;

Tambahkan hak mengeksekusi linked server pada user

USE [master]
GO
GRANT EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER TO user_login_anda;

*jika anda menggunakan domain user maka user_login_anda menjadi [DOMAIN\user_login_anda]

ok itu saja, semoga banyak membantu 😀

Liferay Dan Apache Mod_Proxy

jika anda menginstall liferay namun juga sudah punya web server seperti apache, maka anda dapat menghubungkan apache tersebut dengan liferay. Pada tutorial ini kebutuhan yang perlu disiapkan adalah :

  1. apache 2.2
  2. Liferay v 6.1 (tomcat)
  3. apache mod_proxy

langkah – langkah nya adalah sebagai berikut :

Edit httpd.conf atau sejenisnya

pada tutorial ini saya menggunakan linux opensuse 11.0, dan untuk konfigurasi apache nya berada di /etc/apache/defaultServer.conf

pada konfigurasi, tambahkan alias menggunakan mod_proxy seperti :

ProxyPass /liferay http://127.0.0.1:8080
ProxyPassReverse /liferay http://127.0.0.1:8080

pada perintah tersebut, kita akan membuat alias url /liferay untuk diteruskan ke http://127.0.0.1:8080/ yang mana itu merupakan alamat dan port dari liferay anda (asumsi anda tidak mengubah liferay port , dan anda menginstall liferay di satu server dangan apache).

jangan lupa restart atau reload apache anda

Tambahkan dan Edit portal-ext.properties

selanjutnya kita akan mengedit konfigurasi di sisi liferay, dengan tahapan sebagai berikut :

  1. masuk ke dalam direktori liferay
  2. masuk lagi ke dalam direktori tomcat
  3. masuk ke direktori webapss
  4. masuk ke direktori ROOT
  5. masuk ke direktori WEB-INF
  6. masuk ke direktori classes
  7. buat file baru (jika belum ada) dengan nama portal-ext.properties
  8. lalu edit file portal-ext.properties dan isikan portal.proxy.path=/liferay
  9. save dan exit
  10. restart tomcat

 

done..

sekarang anda dapat mengakses liferay bukan dari http://ip-anda:8080/ , namun bisa dari http://ip-anda/liferay

happy coding all

Tentang dia , kamu dan modularisasi pada sqlserver

Kamu siapa kamu ?  dia siapa dia ?

langsung saja gaiss.. jangan melihat judul itu, langsung saja yang ingin saya bahas adalah :

  1. Stored Procedure
  2. Function

di environtment sqlserver.  tentunya setelah belajar mengenai TSQL , kita harus move on kan ? hehehe.

alasan utama kenapa harus membuat stored procedure dan function adalah modularity yang berujung pada reuse dari stored procedure dan function tersebut.

Stored Procedure

Dengan stored procedure, kita dapat menyimpan sekumpulan TSQL yang kita buat ke dalam sebuah procedure yang tersimpan rapi di hati sqlserver. Dengan tersimpan, kita dapat memanggil manggilnya sesuka hati kita, kapan saja dia siap membantu kita…

sungguh setia bukan !

mari kita mulai dari yang paling simple saja, seperti :

Pada baris 9 dapat dilihat sintaks untuk membuat simple procedure, dimana terdiri dari nama procedure lalu block procedure dimulai dari begin dan diakhiri sampai end, yang mana dalam kasus yang ada di atas kita akan menghitung jumlah baris dari table person yang ada pada database adventure works 2012. Untuk menjalankan stored procedure ini kita dapat menggunakan perintah exec seperti pada baris 21.

Stored procedure dengan parameter

nah yang simple memang sesuai namanya, simple, bagaimana jika jumlah baris yang kita hitung tidak semua person, namun person dengan kriteria tertentu, untuk itu kita dapat menambahkan parameter seperti pada cuplikan berikut :

seperti pada kode baris 9 , kita dapat menambahkan varibael parameter @min_len_middle_name yang bertipe int dengan nilai default 3, sehingga untuk memanggilnya kita dapat menggunakan dengan berbagai cara seperti pada

  • baris 21 tanpa parameter yang berarti memberi nilai default yaitu 3
  • baris 23 dan 25 dengan memberi nilai pada parameter

Stored Procedure dengan nilai kembalian

kedua contoh stored procedure di atas menggunakan tabel sebagai tempat untuk melihat jalannya stored procedure, jika kita tidak menginginkan cara itu, kita dapat membuat stored procedure dengan mempunyai nilai kembalian seperti :

dari kode, saya membuat modifikasi, yaitu sekarang parameter tidak hanya 1 variabel, namun menjadi 3 variabel, dimana 2 variabel pertama adalah varibel input dan  varibel terakhir sebagai variabel output. Perbedaaannya adalah terletak pada keyword OUT yang ada di akhir variabel output.

untuk menjalankan stored procedure ini juga cukup mudah yaitu dengan membuat dulu variabel yang akan kita jadikan sebagai variabel output dan menggunakannya pada EXEC seperti pada 18,23,28.

cukup mudah bukan…

Function

Function itu mirip dengan storedprocedure , bedanya dia pasti punya nilai kembalian dan dijalankannya pada level query. Bagaimana maksudnya, maksudnya seperti :

dimana kita mengguakan pre defined function yaitu AVG untuk menghitung rata – rata pada kolom totaldue pada tabel salesorderheader.

function yang sudah pre defined disebut juga sebagai system functions dan untuk function buatan kita (user) disebut sebagai userd defined function atau disingkat sebagai UDF. contohnya adalah function berikut :

di contoh tersebut fungsi yang kita buat adalah mencari nilai minimum dari 2 variabel,  dan pada baris 22 , fungsi itu digunakan untuk mencari nilai minimum antara kolom freight dan subtotal pada tiap barisnya.

kegunaan lain dari function adalah untuk menghasilkan table , seperti pada contoh berikut :

di mana terlihat bahwa fungsi akan mengembalikan table yang merupakan hasil query. Perbedaannya adalah di sini fungsi tidak membutuhkan begin end melainkan langsung melakukan return table. Untuk memanggil fungsi jenis ini adalah dengan menganggap bahwa fungsi ini sebagai table, seperti pada baris 19.

so mudah bukan melakukan pembuatan fungsi..

happy coding all..

Gentle Introduction to TSQL

Di dunia per basis dataan kita mengenal sebuah bahasa pemrograman deklaratif [1] yaitu SQL. Berbeda sekali yang mungkin suka bergulat di dunia pemrograman ria.. yang mana bahasa yang lebih populer yaitu bahasa dengan paradigma pemrograman imperatif [2] seperti Java, c , c++, c# dan lainnya, walaupun ada juga bahasa yang sifatnya deklaratif misalnya saja haskel.

Sqlserver merupakan salah satu produk dari microsoft yang gencar sekali mengadopsi sql ini,selain linQ [3]. Namun dalam perkembangannya microsoft mungkin melihat dengan sql saja (dan sql spesific sqlserver) belum dapat menyelesaikan semua problem yang bisa saja baru dan kompleks, maka mereka menambahkan fitur baru di implementasi sql mereka yang mereka namakan TSQL (Transact-SQL) [4].

Dengan menggunakan TSQL, kita dapat melakukan pemrograman secara imperatif di environtment sqlserver yang tentunya hal ini akan memperkaya kemampuan sqlserver itu sendiri.

Memulai dengan santai

mari kita lihat cuplikan kode berikut :

Anda dapat mengetikkan cuplikan kode itu langsung di sqlserver management studio atau apapun yang menjadi sql client anda, anggap saja itu bagian dari query , mudah saja.

jika anda lihat pada baris 1, terdapat sebuah deklarasi variabel yang kita beri nama @vars yang akan bertipe float. Untuk tipe tipe lainnya dapat anda lihat di website microsoft [5].

lalu pada baris 3 akan terlihat operasi untuk memberi nilai pada variabel @vars dengan nilai 1, dan akhirnya kita akan tampilkan dalam baris ke 5.

cukup mudah bukan.. apalagi jika anda adalah seseorang dengan basic pemrograman imperatif yang cukup mumpuni pasti akan mudah mengikuti alur nya.

Mengambil nilai langsung dari data

pada cuplikan sebelumnya pada baris ke 3 kita memberi nilai adhoc yang merupakan contoh operasi pemberikan nilai, nah sekarang bagaimana jika kita menginginkan nilai dari variabel @vars berupa nilai yang kita ambil berasal dari data ?, cukup mudah rupanya mari kita lihat cuplikan berikut :

anda dapat lihat caranya benar benar mudah yaitu dengan yang saya lakukan di baris ke 6. Maka kita dapat lihat hasilnya dengan melakukan print di baris ke 8. Untuk melihat nilai variabel anda dapat menggunakan perintah print atau perintah select tergantung pada apa yang ingin anda hasilkan, jika ingin menghasilkan nilai berupa text saja ( akan tampil di tab message) maka anda cukup menggunakan print, namun jika ingin dijadikan sebuah tabel maka anda bisa menggunakan keyword select.

Percabangan itu mudah

Untuk melakukan percabangan caranya juga sangat mudah , seperti pada cuplikan berikut :

dimulai dari baris ke 9 untuk mengisi kondisi dari percabangan dan dilanjutkan dengan membuat begin – end pada baris 10 dan 12, hal ini juga sama dengan kondisi ‘else’ nya.

dalam banyak kasus saya memudahkan pengertian saya mengenai begin  – end dengan menganalogikannya dengan ‘{‘ ‘}’ pada pemrograman berbasis ‘{‘ seperti java , php .

contoh yang lain dapat kita lihat pada cuplikan berikut :

kali ini kita membuat 2 variabel, yang mana keduanya diambil dari data. pada baris ke 12 dan 16 dapat dilihat statement conditional yang menarik, kenapa menarik, karena mungkin tidak terbiasa bagi yang terbiasa menggunakan java atau php.

  1. dimana jika kita ingin melakukan conditional checking pada 2 kondisi dapat menggunakan keyword and
  2. untuk melakukan checking ‘!=’ kita dapat menggunakan ‘<>’
  3. untuk melakukan checking ‘==’ kita dapat menggunakan ‘=’

Perulangan yang juga mudah dilupakan

Pemrograman tanpa perulangan seperti sayur tanpa garam.. kurang mantap goyangannya..

mari kita lihat cuplikan berikut :

cara nya cukup mudah bukan ? , saya menggunakan metode paling simple dalam perulangan yaitu metode while do.

untuk baris 12 anda dapat melihat sebuah operasi baru yaitu cast. Dengan cast saya dapat mengubah sebuah tipe data variabel ke tipe data tertentu, dan yang saya lakukan adalah merubah tipe data variable tsb (int) ke dalam varchar agar dapat dilakukan proses concatenate dengan operator +. Sebenarnya kalo dilihat lagi mirip dan tidak mirip dengan java, di java kita dapat melakukan System.out.print(“Halo umur saya “+umur); , bedanya adalah di java terjadi automatic conversion antara tipe data tsb, dimana tipe data primiritf akan di ubah menjadi String, sedangkan jika tipe data referensional akan digunakan method toString().

Sepertinya contoh di atas terlalu simple, bagaiamana jika ingin melihat atau melakukan operasi yang langsung melibatkan data dari table ? mari kita lihat contoh yang lebih kompleks :

tujuan dari kode tsb adalah untuk menampilkan totaldue dari 10 transaksi terakhir.tahap pertama dapat anda lihat di baris 8 , saya menggunakan tipe data table dengan kolom yaitu nomor(int) dan id(int), yang bertujuan menyimpan nomor urut dan id dari salesorder. Untuk mengisi variabel ini, operasi insert into select digunakan, seperti pada baris 15, dan untuk mengisi nomor urut digunakan function row_number() yang sudah saya bahas pada postingan membuat query paging di sqlserver sebelumnya.  Dan proses utama terdapat di baris 25 – 29.

Perulangan menggunakan cursor

di Sqlserver kita dapat menggunakan cursor untuk melakukan perulangan seperti kode terakhir yang kita lihat. Contohnya sebagai berikut :

kita dapat mendefinisikan cursor seperti pada baris 7 dan 9, lalu dilanjutkan pada proses penggunaan cursor pada baris 16 yang sebelumnya kita buka pada baris 15. Proses tersebut akan memindahkan nilai query pada cursor dan disimpan pada sebuah variabel yang nanti akan kita tampilkan pada baris 20.

Beberapa catatan untuk penggunaan cursor ini adalah seringkali programmer lupa untuk menambahkan perintah yang sama pada baris 16 ke baris 22 (sebelum end) , hal itu dibutuhkan untuk cursor tetap berjalan.

catatan lainnya adalah jangan lupa untuk menutup dan melakukan deallocate pada variabel cursor tersebut.

mudah bukan TSQL.. , jadi gak perlu dikit dikit php , dikit dikit java …….

happy coding semua…

referensi

[1] https://en.wikipedia.org/wiki/Declarative_programming

[2] https://en.wikipedia.org/wiki/Imperative_programming

[3] https://msdn.microsoft.com/en-us/library/bb397926.aspx

[4] https://en.wikipedia.org/wiki/Transact-SQL

[5] https://msdn.microsoft.com/en-us/library/ms187752.aspx

Membuat query lebih simple dengan Common Table Expression

setelah melihat bagaimana menggunakan paging di sqlserver, sekarang saya akan mencoba mengulas sedikit mengenai kemampuan sqlserver yang lain yaitu Common Table Expression (CTE) [1].  Dengan CTE ini kita dapat membuat query kita lebih simple.

Contoh Sederhana CTE

mari ambil contoh salah satu query dari postingan saya sebelumnya, yaitu query

pada query itu kita akan menambahkan kolom nomor baris, dan selanjutnya akan dilanjutkan dengan query

yang akan melakukan seleksi baris yang akan kita ambil dan tampilkan.

nah sekarang kita coba dengan menggunakan cara yang sedikit berebeda , yaitu menggunakan CTE. Untuk melakukan itu, kita membutuhkan keyword with.

berikut langkah – langkah sederahananya :

  1. Buat dahulu CTE tersebut yang akan menggunakan nama alias tertentu
  2. Buat query yang memanggil alias tersebut sebagai sebuah table.

tahap 1 dimulai dari kita membuat CTE nya, seperti pada query :

setelah itu kita dapat menambahkan query dibawahnya dengan memanggil CTE tersebut, seperti pada query penuh seperti :

maka kita dapat menggunakan CTE sebagai alternatif dalam menuliskan paging tanpa nested query.

Menggunakan CTE untuk mengaggregasi

contoh pada web microsoft [1]  memberikan contoh yang baik untuk hal ini , misalnya saja kita mempunyai query untuk menghasilkan jumlah penjualan (transaksi) per Sales, maka dapat kita selesaikan dengan query

dan untuk menghitung rata – rata penjualan per sales maka akan kita dapatkan dengan query

 

yang mana sama dengan query

jika kita tidak menggunakan CTE.

Multiple CTE

misalkan saja berdasarkan contoh di atas, kita ingin melihat siapa saja yang sales yang diatas rata – rata maka dengan mudah kita mendefinisikan query sebagai berikut :

 

jauh lebih simple daripada kita menggunakan nested query berikut :

Hirarki dengan CTE

masalah ini sangat terkait dengan masalah yang terkenal di dunia design yaitu masalah rekursif, contohnya adalah design pada masalah employee-manager [2]. Dalam gambar kira2 bisa digambarkan seperti :

recursive-uml

untuk menggenerate masalah ini sebelumnya kita perlu membuat tabel untuk itu, karena di adventure works 2012 tidak ada permasalahan rekursif ini secara mudah untuk dilihat. Untuk itu kembali kita lihat contoh dari website microsoft [1]. Yaitu menggunakan query :

dengan ini kita akan membuat table myEmployees yang berisikan beberapa data dummy.

maka untuk menampilkan list employee beserta managernya kita bisa memanfaatkan query

di dalam query itu kita menggunakan (memanggil ) CTE di dalam deklarasi CTE itu sendiri

so asik bukan.. silakan untuk melihat – lihat contoh lain pada [1].

Referensi

[1]. https://msdn.microsoft.com/en-us/library/ms175972.aspx

[2] http://www.tomjewett.com/dbdesign/dbdesign.php?page=recursive.php

Paging di SQLSERVER

seandainya kita akan membuat halaman web untuk menampilkan data penjualan dari database adventurework 2012 [1] seperti pada query :

maka kit memerlukan solusi paging untuk itu. Karena jika query itu dijalankan maka akan menampilkan data sebanyak 121.317 baris.. tentu hal yang mustahil menampilkan data sebanyak itu dalam 1 halaman web saja.

pada sistem database yang lain misalnya mysql , postgresql atau sqlite maka akan dikenal keyword limit dan offset untuk melakukan paging [2], namun hal itu tidak berlaku di sqlserver.

untuk melakukan paging di sqlserver kita memerlukan beberapa langkah secara umum :

  1. Jadikan query menjadi nested query
  2. Berikan nomor pada masing – masing baris hasil nested query pada query baru kita
  3. filter hasil query berdasarkan nomor tersebut

untuk proses nomor 2 , kita dapat memanfaatkan function dari sqlserver yaitu row_number()  [3], seperti pada query :

setelah melakukan number maka kita dapat jadikan hal tersebut sebagai nested query dan memfilter datanya berdasarkan kolom number seperti pada query :

 

Paging pada SQLSERVER 2012

khusus pada sqlserver 2012 ke atas, paging dapat dilakukan dengan lebih simple dengan memanfaatkan keyword offset fetch [4], dengan langkah – langkah secara umum sebagai berikut :

  1. buat query dasar lengkap dengan pengurutan
  2. tambahkan keyword offset
  3. tambahkan keyword fetch

seperti pada query :

 

mudah bukan….

happy coding semua…

Referensi

[1] http://blog.sqlauthority.com/2012/03/15/sql-server-install-samples-database-adventure-works-for-sql-server-2012/

[2] http://stackoverflow.com/questions/971964/limit-10-20-in-sql-server

[3] https://msdn.microsoft.com/en-us/library/ms186734.aspx

[4] https://technet.microsoft.com/en-us/library/gg699618%28v=sql.110%29.aspx